Re: Is it possible to sort strings in EBCDIC order in PostgreSQL server?

[Laurenz Albe]

Tsunakawa, Takayuki wrote:
> It is embarrassing to ask such a thing, but is there any way to sort 
> character column values in EBCDIC order inside the PostgreSQL server?
> i.e. is it possible to use EBCDIC collation in PostgreSQL, say, by using ICU 
> or something?  We need to run on a certain mainframe.
> 
> I see almost no hope from the following, but let me make sure.

That should not be too difficult.  PostgreSQL is extensible!

Just define operators that implement <, <=, =, >= and > for EBCDIC,
create an operator class for these (for indexing) and use
ORDER BY ... USING.

Yours,
Laurenz Albe

Re: Display table entries using partial column entry

[Luuk]

On 11-12-17 00:39, Gmail wrote:
> 
> 
> On Dec 10, 2017, at 4:10 PM, Melvin Davidson  > wrote:
> 
>>
>>
>> On Sun, Dec 10, 2017 at 4:50 PM, Sherman Willden
>> mailto:operasopra...@gmail.com>> wrote:
>>
...

>> Thank you;
>>
>> Sherman
>>
>>
>> Sherman,
>> In the future, please be kind enough to provide PostgreSQL version and
>> O/S when posting to this listing.
>> Also include COLUMN HEADERS with all data and present the data
>> formatted so it is easily readable by humans.
>>
>> Presuming the # is your column divider, and the data you have provided
>> looks like this:
>> id            #aria                         #artist
>>       #a_artist     #album_title
>> cbiylm06    #Caro mio ben                  #Cecilia
>> Bartoli #         #Se tu m'ami
>> cbiylm07    #Pur dicesti, o bocca bella    #Cecilia Bartoli    
>> #             #Se tu m'ami
>> cbiylm08    #Intorno all'idol mio           #Cecilia
>> Bartoli #             #Se tu m'ami
>> cbiylm09    #Nel cor più non mi sento  #Cecilia Bartoli   
>> #         #Se tu m'ami
>> cbiylm10    #Il mio ben quando ve       #Cecilia Bartoli
>> #         #Se tu m'ami
>> cbiylm11    #O Leggiadri Occhi Belli    #Cecilia Bartoli
>> #         #Se tu m'ami
>> cbiylm12    #Il mio bel foco          #Cecilia
>> Bartoli #         #Se tu m'ami
>>
>> Then all you really need is:
>> SELECT *
>>    FROM aria_precis
>> WHERE aria LIKE '%mio ben%';
>>
>> Pattern Matching
>> https://www.postgresql.org/docs/9.6/static/functions-matching.html
> 
> Where I hop you will find the tilde operator ('~', '~*').  Why more fun.


You should be aware of case insensitivity.

LIKE '%mio ben%', will only match lower case

and, PostgreSQL-specific:
ILIKE '%mio ben%', will match any case
~ 'mio ben', will only match lower case
~* 'mio ben', will match any case

Unsigned RPM's ?

[Patrik Martinsson]

Hello,

If I'm not mistaken the latest available rpms for Red Hat 7.3 is missing
the signature, doing a simple

$ > rpm -qpi
https://download.postgresql.org/pub/repos/yum/9.3/redhat/rhel-7.3-x86_64/postgresql93-server-9.3.20-3PGDG.rhel7.x86_64.rpm
"

Gives me,
$ > Name: postgresql93-server
$ > Version : 9.3.20
$ > Release : 3PGDG.rhel7
$ > Architecture: x86_64
$ > Install Date: (not installed)
$ > Group   : Applications/Databases
$ > Size: 16551335
$ > License : PostgreSQL
$ > Signature   : (none)
$ > Source RPM  : postgresql93-9.3.20-3PGDG.rhel7.src.rpm
$ > Build Date  : tor  7 dec 2017 22:41:49
$ > Build Host  : koji-rhel7-x86-64-pgbuild
$ > Relocations : (not relocatable)
$ > URL : http://www.postgresql.org/
$ > Summary : The programs needed to create and run a PostgreSQL server
$ > Description :
$ > PostgreSQL is an advanced Object-Relational database management system
(DBMS).
$ > The postgresql93-server package contains the programs needed to create
$ > and run a PostgreSQL server, which will in turn allow you to create
$ > and maintain PostgreSQL databases.

Notice the Signature "(none)".
Doing the same command on previous rpm's reveals a the signature "DSA/SHA1,
ons  8 nov 2017 21:52:18, Key ID 1f16d2e1442df0f8"

Am I missing something ?

Best regards,
Patrik Martinsson
Sweden

Re: Windows XP to Win 10 migration issue

[Vincent Veyron]

On Sat, 9 Dec 2017 10:11:42 -0600
Dale Seaburg  wrote:

>   No Go!  Would not start. 

Any error message in your logs?

I would certainly second Scott's suggestion to check the processors. I've had 
to do what you describe once, and it took me four machines before I got one 
that would start postgresql with my rescued data folder.

You might have better luck finding an old XP machine similar to your deceased 
one, installing postgresql on it, and using that to start your cluster.


-- 
Bien à vous, Vincent Veyron 
https://marica.fr/
Logiciel de gestion des sinistres assurances, des dossiers contentieux et des 
contrats pour le service juridique

PostgreSQL is so hip again

[Ray Stell]

InfoWorld on postgresql:

https://www.infoworld.com/article/3240064/sql/why-old-school-postgresql-is-so-hip-again.html?idg_eid=d088f0ac35204f7a4055d4fd64053ac3&email_SHA1_lc=d3136cf9e64ce7067d0fe82239bbd76e165586bc&cid=ifw_nlt_infoworld_developer_2017-12-08&utm_source=Sailthru&utm_medium=email&utm_campaign=InfoWorld%20App%20Dev%20Report%202017-12-08&utm_term=infoworld_developer

Re: Is it possible to sort strings in EBCDIC order in PostgreSQL server?

[Tom Lane]

"Tsunakawa, Takayuki"  writes:
> It is embarrassing to ask such a thing, but is there any way to sort 
> character column values in EBCDIC order inside the PostgreSQL server?  i.e. 
> is it possible to use EBCDIC collation in PostgreSQL, say, by using ICU or 
> something?  We need to run on a certain mainframe.

> I see almost no hope from the following, but let me make sure.
> https://www.postgresql.org/message-id/flat/28548.1424884373%40sss.pgh.pa.us#28548.1424884...@sss.pgh.pa.us

> "Hmm ... EBCDIC ... is that mandatory?  Because there are a pretty large
> number of ASCII dependencies in PG, mostly arising from our support of
> multibyte character sets, which are all expected to be ASCII supersets.
> If there's not a way to run the server process in ASCII-land, you're
> likely dead in the water.  I would not want to try to ferret out all
> the dependencies."

As I recall, that question was about converting the server to use
EBCDIC strings everywhere.  That seems impractical for the reasons
I mentioned.  But you could probably sort according to EBCDIC order
if you were willing to accept some inefficiency: internal to the
comparison function, convert each string to EBCDIC on each call.

Another way you could imagine is to store EBCDIC strings embedded
in bytea values.

regards, tom lane

Re: Removing INNER JOINs

[Tom Lane]

Jim Finnerty  writes:
> Great example, David.  The planner can detect whether a SELECT statement
> contains a volatile function, and can disable the proposed redundant
> inner-join optimization in that case.  

> If necessary, the planner could also check that the FK constraint is not
> DEFERRED, but if there are no volatile functions and the SELECT statement
> can't see an inconsistent state created by any other transaction, I think
> that just checking for volatile functions and not being inside a DML
> transaction would be sufficient.

I don't think you're thinking nearly hard enough about what would break
this.  The planner does not have much insight into the context a
statement is being used in (e.g. whether we're inside some kind of
PL function).  Nor does it get to make assumptions about whether the plan
will be used inside a transaction block or not.

regards, tom lane

Re: PostgreSQL is so hip again

[Achilleas Mantzios]

On 11/12/2017 16:43, Ray Stell wrote:

InfoWorld on postgresql:

https://www.infoworld.com/article/3240064/sql/why-old-school-postgresql-is-so-hip-again.html?idg_eid=d088f0ac35204f7a4055d4fd64053ac3&email_SHA1_lc=d3136cf9e64ce7067d0fe82239bbd76e165586bc&cid=ifw_nlt_infoworld_developer_2017-12-08&utm_source=Sailthru&utm_medium=email&utm_campaign=InfoWorld%20App%20Dev%20Report%202017-12-08&utm_term=infoworld_developer 




Nice article :)


--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Unsigned RPM's ?

[Patrik Martinsson]

I'm going ahead and answering my self.
Apparently this issue was already posted in the postgresql-yum-list.

https://www.postgresql.org/message-id/flat/CAAQZgJyWQdfCpNXFaT6vtWGuztWCMQf_kakjxjwTkUFJidCtqg%40mail.gmail.com#caaqzgjywqdfcpnxfat6vtwguztwcmqf_kakjxjwtkufjidc...@mail.gmail.com

// Patrik

On Mon, Dec 11, 2017 at 11:19 AM Patrik Martinsson <
martinsson.pat...@gmail.com> wrote:

> Hello,
>
> If I'm not mistaken the latest available rpms for Red Hat 7.3 is missing
> the signature, doing a simple
>
> $ > rpm -qpi
> https://download.postgresql.org/pub/repos/yum/9.3/redhat/rhel-7.3-x86_64/postgresql93-server-9.3.20-3PGDG.rhel7.x86_64.rpm
> "
>
> Gives me,
> $ > Name: postgresql93-server
> $ > Version : 9.3.20
> $ > Release : 3PGDG.rhel7
> $ > Architecture: x86_64
> $ > Install Date: (not installed)
> $ > Group   : Applications/Databases
> $ > Size: 16551335
> $ > License : PostgreSQL
> $ > Signature   : (none)
> $ > Source RPM  : postgresql93-9.3.20-3PGDG.rhel7.src.rpm
> $ > Build Date  : tor  7 dec 2017 22:41:49
> $ > Build Host  : koji-rhel7-x86-64-pgbuild
> $ > Relocations : (not relocatable)
> $ > URL : http://www.postgresql.org/
> $ > Summary : The programs needed to create and run a PostgreSQL server
> $ > Description :
> $ > PostgreSQL is an advanced Object-Relational database management system
> (DBMS).
> $ > The postgresql93-server package contains the programs needed to create
> $ > and run a PostgreSQL server, which will in turn allow you to create
> $ > and maintain PostgreSQL databases.
>
> Notice the Signature "(none)".
> Doing the same command on previous rpm's reveals a the signature
> "DSA/SHA1, ons  8 nov 2017 21:52:18, Key ID 1f16d2e1442df0f8"
>
> Am I missing something ?
>
> Best regards,
> Patrik Martinsson
> Sweden
>

How to know if a database has changed

[marcelo]

The installation I'm planning will manage several databases, but not all 
of them will change every day.
In order to planning/scripting the pg_dump usage, I would need to know 
which databases had some change activity at the end of some day.

How can it be done?
TIA

pg_dump and logging

[marcelo]

When pg_dump runs on a database, is it warranted that the log is fully 
impacted, or at least, taken into account for the dumping?

TIA

Re: How to know if a database has changed

[Karsten Hilbert]

On Mon, Dec 11, 2017 at 01:48:44PM -0300, marcelo wrote:

> The installation I'm planning will manage several databases, but not all of
> them will change every day.
> In order to planning/scripting the pg_dump usage, I would need to know which
> databases had some change activity at the end of some day.
> How can it be done?

Implement INSERT/UPDATE/DELETE/TRUNCATE triggers on all
tables and DDL triggers on CREATE, ALTER, DROP, SECURITY
LABEL, COMMENT, GRANT or REVOKE.

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: How to know if a database has changed

[Walter Nordmann]

Hi,

a) create triggers on some tables (on update, on delete ...).

let the triggers write some log, check the log or let the triggers 
update one central table adding database, tablenames with "table ... in 
database ... has been updated, modified'


b) enable detailed loggin and check the system log.

c) both ;)

Regards
walter

Am 11.12.2017 um 17:48 schrieb marcelo:
The installation I'm planning will manage several databases, but not 
all of them will change every day.
In order to planning/scripting the pg_dump usage, I would need to know 
which databases had some change activity at the end of some day.

How can it be done?
TIA

Re: pg_dump and logging

[Karsten Hilbert]

On Mon, Dec 11, 2017 at 01:51:59PM -0300, marcelo wrote:

> When pg_dump runs on a database, is it warranted that the log is fully
> impacted, or at least, taken into account for the dumping?

As per the second sentence in the Description section of the
Fine Manual.

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: How to know if a database has changed

[James Keener]

The two non elegant ways I can think of is checking the modification time on 
the files representing the database and a query that checks the pk of all 
tables. If they're ordered pk you could store the max of them and then compare, 
otherwise the max of an updated at column would work as well.

Jim

On December 11, 2017 11:48:44 AM EST, marcelo  wrote:
>The installation I'm planning will manage several databases, but not
>all 
>of them will change every day.
>In order to planning/scripting the pg_dump usage, I would need to know 
>which databases had some change activity at the end of some day.
>How can it be done?
>TIA

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

Re: How to know if a database has changed

[Jaime Casanova]

On 11 December 2017 at 11:48, marcelo  wrote:
> The installation I'm planning will manage several databases, but not all of
> them will change every day.
> In order to planning/scripting the pg_dump usage, I would need to know which
> databases had some change activity at the end of some day.
> How can it be done?

Hi,

Just keep a snapshot of pg_stat_database view
(https://www.postgresql.org/docs/10/static/monitoring-stats.html#PG-STAT-DATABASE-VIEW)

At the end of the day compare the counters of
tup_inserted/tup_updated/tup_deleted or just xact_commit/xact_rollback
if the snapshot you kept is different from the current values there
were modifications on the database.

Maybe there are some caveats, for example CREATE commands are included
because they insert data in table catalogs? i don't know, is up to you
to check that

-- 
Jaime Casanova  www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: How to know if a database has changed

[Andreas Kretschmer]

Am 11.12.2017 um 17:48 schrieb marcelo:
The installation I'm planning will manage several databases, but not 
all of them will change every day.
In order to planning/scripting the pg_dump usage, I would need to know 
which databases had some change activity at the end of some day.

How can it be done?
TIA



I think you can use logical replication. Take a view at 
https://github.com/eulerto/wal2json (for instance, there are other 
solutions available), just capture the wal and check for changes for 
every database.


it's just a rough idea...



Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: How to know if a database has changed

[Andreas Kretschmer]

Am 11.12.2017 um 18:26 schrieb Andreas Kretschmer:
it's just a rough idea... 


... and not perfect, because you can't capture ddl in this way.


Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: Is it possible to sort strings in EBCDIC order in PostgreSQL server?

[Peter Geoghegan]

On Sun, Dec 10, 2017 at 11:09 PM, Tsunakawa, Takayuki
 wrote:
> It is embarrassing to ask such a thing, but is there any way to sort 
> character column values in EBCDIC order inside the PostgreSQL server?  i.e. 
> is it possible to use EBCDIC collation in PostgreSQL, say, by using ICU or 
> something?  We need to run on a certain mainframe.

There is no need for embarrassment. I think that you mean that you'd
like to get behavior equivalent to the "C" locale with an EBCDIC
encoding. Right? I think that you could use ICU to do this with some
work. ICU collations are not tied to a particular encoding, unlike the
libc collations (they support most but not all collations).

That said, the idea of an "EBCDIC collation" seems limiting. Why
should a system like DB2 for the mainframe (that happens to use EBCDIC
as its encoding) not have a more natural, human-orientated collation
even while using EBCDIC? ISTM that the point of using the "C" locale
(with EBDIC or with UTF-8 or with any other encoding) is to get a
performance benefit where the actual collation's behavior doesn't
matter much to users. Are you sure it's really important to be
*exactly* compatible with EBCDIC order? As long as you're paying for a
custom collation, why not just use a collation that is helpful to
humans?

-- 
Peter Geoghegan

SearchSysCache() tutorial?

[Paul Ramsey]

Is there anywhere any documentation on SearchSysCache? I find I end up
on these long spelunking expeditions through the code base for a
particular problem, find the answer after many hours, then forget
everything I learned because I don't exercise my knowledge frequently
enough. A decent reference guide would help a lot. What do the various
SysCacheIdentifier numbers mean/search, some examples, of usage, etc,
etc.

I can accept if there are not, but it would be a shame to keep on
hunting like this if there were a good reference lying around.

Thanks!
P

Re: SearchSysCache() tutorial?

[Paul Ramsey]

On Mon, Dec 11, 2017 at 11:25 AM, Paul Ramsey  wrote:
> Is there anywhere any documentation on SearchSysCache? I find I end up
> on these long spelunking expeditions through the code base for a
> particular problem, find the answer after many hours, then forget
> everything I learned because I don't exercise my knowledge frequently
> enough. A decent reference guide would help a lot. What do the various
> SysCacheIdentifier numbers mean/search, some examples, of usage, etc,
> etc.
>
> I can accept if there are not, but it would be a shame to keep on
> hunting like this if there were a good reference lying around.

My particular hunt today is "for a given table relation, find any
indexes that use the gist access method and are on a single attribute
of type geometry".

Re: SearchSysCache() tutorial?

[Melvin Davidson]

On Mon, Dec 11, 2017 at 2:26 PM, Paul Ramsey 
wrote:

> On Mon, Dec 11, 2017 at 11:25 AM, Paul Ramsey 
> wrote:
> > Is there anywhere any documentation on SearchSysCache? I find I end up
> > on these long spelunking expeditions through the code base for a
> > particular problem, find the answer after many hours, then forget
> > everything I learned because I don't exercise my knowledge frequently
> > enough. A decent reference guide would help a lot. What do the various
> > SysCacheIdentifier numbers mean/search, some examples, of usage, etc,
> > etc.
> >
> > I can accept if there are not, but it would be a shame to keep on
> > hunting like this if there were a good reference lying around.
>
> My particular hunt today is "for a given table relation, find any
> indexes that use the gist access method and are on a single attribute
> of type geometry".
>
>
>My particular hunt today is "

*for a given table relation, find any >indexes that use the gist access
method and are on a single attribute >of type geometry".*

For that information, you are better off querying the system catalogs!

*https://www.postgresql.org/docs/9.6/static/catalogs.html
*

adjust the WHERE clause below to include the attribute you are looking for.

SELECT n.nspname as schema,
   i.relname as table,
   i.indexrelname as index,
   i.idx_scan,
   i.idx_tup_read,
   i.idx_tup_fetch,
   CASE WHEN idx.indisprimary
THEN 'pkey'
WHEN idx.indisunique
THEN 'uidx'
ELSE 'idx'
END AS type,
   idx.indisexclusion,
   pg_get_indexdef(idx.indexrelid),
   CASE WHEN idx.indisvalid
THEN 'valid'
ELSE 'INVALID'
END as statusi,
   pg_relation_size(quote_ident(n.nspname)|| '.' ||
quote_ident(i.relname)) as size_in_bytes,
   pg_size_pretty(pg_relation_size(quote_ident(n.nspname)|| '.' ||
quote_ident(i.relname))) as size
  FROM pg_stat_all_indexes i
  JOIN pg_class c ON (c.oid = i.relid)
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
  JOIN pg_index idx ON (idx.indexrelid =  i.indexrelid )
 WHERE i.relname = 'your_table'
   AND n.nspname NOT LIKE 'pg_%'
   AND  pg_get_indexdef(idx.indexrelid) LIKE '%gist%'
ORDER BY 1, 2, 3;

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: SearchSysCache() tutorial?

[Paul Ramsey]

> On Dec 11, 2017, at 11:37 AM, Melvin Davidson  wrote:
> 
> 
> 
> On Mon, Dec 11, 2017 at 2:26 PM, Paul Ramsey  > wrote:
> On Mon, Dec 11, 2017 at 11:25 AM, Paul Ramsey  > wrote:
> > Is there anywhere any documentation on SearchSysCache? I find I end up
> > on these long spelunking expeditions through the code base for a
> > particular problem, find the answer after many hours, then forget
> > everything I learned because I don't exercise my knowledge frequently
> > enough. A decent reference guide would help a lot. What do the various
> > SysCacheIdentifier numbers mean/search, some examples, of usage, etc,
> > etc.
> >
> > I can accept if there are not, but it would be a shame to keep on
> > hunting like this if there were a good reference lying around.
> 
> My particular hunt today is "for a given table relation, find any
> indexes that use the gist access method and are on a single attribute
> of type geometry".
> 
> 
> >My particular hunt today is "for a given table relation, find any
> >indexes that use the gist access method and are on a single attribute
> >of type geometry".
> 
> For that information, you are better off querying the system catalogs!
> 
> https://www.postgresql.org/docs/9.6/static/catalogs.html 
> 
> 
> adjust the WHERE clause below to include the attribute you are looking for.
> 
> SELECT n.nspname as schema,
>i.relname as table,
>i.indexrelname as index,
>i.idx_scan,
>i.idx_tup_read,
>i.idx_tup_fetch,
>CASE WHEN idx.indisprimary
> THEN 'pkey'
> WHEN idx.indisunique
> THEN 'uidx'
> ELSE 'idx'
> END AS type,
>idx.indisexclusion,
>pg_get_indexdef(idx.indexrelid),
>CASE WHEN idx.indisvalid
> THEN 'valid'
> ELSE 'INVALID'
> END as statusi,
>pg_relation_size(quote_ident(n.nspname)|| '.' || 
> quote_ident(i.relname)) as size_in_bytes,
>pg_size_pretty(pg_relation_size(quote_ident(n.nspname)|| '.' || 
> quote_ident(i.relname))) as size
>   FROM pg_stat_all_indexes i
>   JOIN pg_class c ON (c.oid = i.relid)
>   JOIN pg_namespace n ON (n.oid = c.relnamespace)
>   JOIN pg_index idx ON (idx.indexrelid =  i.indexrelid )
>  WHERE i.relname = 'your_table'
>AND n.nspname NOT LIKE 'pg_%'
>AND  pg_get_indexdef(idx.indexrelid) LIKE '%gist%'
> ORDER BY 1, 2, 3;

Thanks. I’m working on doing this at the C level however, so using syscache 
seems like the right way to go about it. I’d like to avoid doing an SPI thing, 
if I can, tho I suppose I could always suck it up and just do that.

P

Re: SearchSysCache() tutorial?

[Melvin Davidson]

There is no guarantee that information will be in syscache at any point in
time. It will, however, always be in the postgreSQL catalogs. That is the
whole point
of having them, and the SQL language.

On Mon, Dec 11, 2017 at 2:39 PM, Paul Ramsey 
wrote:

>
> On Dec 11, 2017, at 11:37 AM, Melvin Davidson 
> wrote:
>
>
>
> On Mon, Dec 11, 2017 at 2:26 PM, Paul Ramsey 
> wrote:
>
>> On Mon, Dec 11, 2017 at 11:25 AM, Paul Ramsey 
>> wrote:
>> > Is there anywhere any documentation on SearchSysCache? I find I end up
>> > on these long spelunking expeditions through the code base for a
>> > particular problem, find the answer after many hours, then forget
>> > everything I learned because I don't exercise my knowledge frequently
>> > enough. A decent reference guide would help a lot. What do the various
>> > SysCacheIdentifier numbers mean/search, some examples, of usage, etc,
>> > etc.
>> >
>> > I can accept if there are not, but it would be a shame to keep on
>> > hunting like this if there were a good reference lying around.
>>
>> My particular hunt today is "for a given table relation, find any
>> indexes that use the gist access method and are on a single attribute
>> of type geometry".
>>
>>
> >My particular hunt today is "
>
> *for a given table relation, find any >indexes that use the gist access
> method and are on a single attribute >of type geometry".*
>
> For that information, you are better off querying the system catalogs!
>
> *https://www.postgresql.org/docs/9.6/static/catalogs.html
> *
>
> adjust the WHERE clause below to include the attribute you are looking for.
>
> SELECT n.nspname as schema,
>i.relname as table,
>i.indexrelname as index,
>i.idx_scan,
>i.idx_tup_read,
>i.idx_tup_fetch,
>CASE WHEN idx.indisprimary
> THEN 'pkey'
> WHEN idx.indisunique
> THEN 'uidx'
> ELSE 'idx'
> END AS type,
>idx.indisexclusion,
>pg_get_indexdef(idx.indexrelid),
>CASE WHEN idx.indisvalid
> THEN 'valid'
> ELSE 'INVALID'
> END as statusi,
>pg_relation_size(quote_ident(n.nspname)|| '.' ||
> quote_ident(i.relname)) as size_in_bytes,
>pg_size_pretty(pg_relation_size(quote_ident(n.nspname)|| '.' ||
> quote_ident(i.relname))) as size
>   FROM pg_stat_all_indexes i
>   JOIN pg_class c ON (c.oid = i.relid)
>   JOIN pg_namespace n ON (n.oid = c.relnamespace)
>   JOIN pg_index idx ON (idx.indexrelid =  i.indexrelid )
>  WHERE i.relname = 'your_table'
>AND n.nspname NOT LIKE 'pg_%'
>AND  pg_get_indexdef(idx.indexrelid) LIKE '%gist%'
> ORDER BY 1, 2, 3;
>
>
> Thanks. I’m working on doing this at the C level however, so using
> syscache seems like the right way to go about it. I’d like to avoid doing
> an SPI thing, if I can, tho I suppose I could always suck it up and just do
> that.
>
> P
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: SearchSysCache() tutorial?

[Andres Freund]

On 2017-12-11 14:42:33 -0500, Melvin Davidson wrote:
> There is no guarantee that information will be in syscache at any point in
> time. It will, however, always be in the postgreSQL catalogs. That is the
> whole point
> of having them, and the SQL language.

This doesn't make much sense. Paul's working on a C extension that's
then usable via SQL. Using the syscaches is the perfectly right thing to
do.

- Andres

Re: SearchSysCache() tutorial?

[Andres Freund]

On 2017-12-11 11:26:51 -0800, Paul Ramsey wrote:
> On Mon, Dec 11, 2017 at 11:25 AM, Paul Ramsey  
> wrote:
> > Is there anywhere any documentation on SearchSysCache? I find I end up
> > on these long spelunking expeditions through the code base for a
> > particular problem, find the answer after many hours, then forget
> > everything I learned because I don't exercise my knowledge frequently
> > enough. A decent reference guide would help a lot. What do the various
> > SysCacheIdentifier numbers mean/search, some examples, of usage, etc,
> > etc.
> >
> > I can accept if there are not, but it would be a shame to keep on
> > hunting like this if there were a good reference lying around.
> 
> My particular hunt today is "for a given table relation, find any
> indexes that use the gist access method and are on a single attribute
> of type geometry".

I don't think there's a way to do this with a single syscache, there
won't be an index than can cover all of these. I'd suggest using
RelationGetIndexList(), and then filtering for gist and attribute type
on the returned value.

Greetings,

Andres Freund

Re: SearchSysCache() tutorial?

[Paul Ramsey]

> On Dec 11, 2017, at 12:40 PM, Andres Freund  wrote:
> 
> On 2017-12-11 11:26:51 -0800, Paul Ramsey wrote:
>> On Mon, Dec 11, 2017 at 11:25 AM, Paul Ramsey  
>> wrote:
>>> Is there anywhere any documentation on SearchSysCache? I find I end up
>>> on these long spelunking expeditions through the code base for a
>>> particular problem, find the answer after many hours, then forget
>>> everything I learned because I don't exercise my knowledge frequently
>>> enough. A decent reference guide would help a lot. What do the various
>>> SysCacheIdentifier numbers mean/search, some examples, of usage, etc,
>>> etc.
>>> 
>>> I can accept if there are not, but it would be a shame to keep on
>>> hunting like this if there were a good reference lying around.
>> 
>> My particular hunt today is "for a given table relation, find any
>> indexes that use the gist access method and are on a single attribute
>> of type geometry".
> 
> I don't think there's a way to do this with a single syscache, there
> won't be an index than can cover all of these. I'd suggest using
> RelationGetIndexList(), and then filtering for gist and attribute type
> on the returned value.

Thanks, A, I seem to be on the right path then. Loop through all indexes in 
RelationGetIndexList(), for those that have a relam == GIST_AM_OID, loop 
through the associated columns (looks like I actually have to use a SysScanDesc 
on AttributeRelationId) and look for atts that have the type I’m interested in, 
and if I find one, yay, we have a winner.
Thanks,
P

Re: SearchSysCache() tutorial?

[Andres Freund]

On 2017-12-11 12:48:27 -0800, Paul Ramsey wrote:
> 
> > On Dec 11, 2017, at 12:40 PM, Andres Freund  wrote:
> > 
> > On 2017-12-11 11:26:51 -0800, Paul Ramsey wrote:
> >> On Mon, Dec 11, 2017 at 11:25 AM, Paul Ramsey  
> >> wrote:
> >>> Is there anywhere any documentation on SearchSysCache? I find I end up
> >>> on these long spelunking expeditions through the code base for a
> >>> particular problem, find the answer after many hours, then forget
> >>> everything I learned because I don't exercise my knowledge frequently
> >>> enough. A decent reference guide would help a lot. What do the various
> >>> SysCacheIdentifier numbers mean/search, some examples, of usage, etc,
> >>> etc.
> >>> 
> >>> I can accept if there are not, but it would be a shame to keep on
> >>> hunting like this if there were a good reference lying around.
> >> 
> >> My particular hunt today is "for a given table relation, find any
> >> indexes that use the gist access method and are on a single attribute
> >> of type geometry".
> > 
> > I don't think there's a way to do this with a single syscache, there
> > won't be an index than can cover all of these. I'd suggest using
> > RelationGetIndexList(), and then filtering for gist and attribute type
> > on the returned value.
> 
> Thanks, A, I seem to be on the right path then. Loop through all
> indexes in RelationGetIndexList(), for those that have a relam ==
> GIST_AM_OID, loop through the associated columns (looks like I
> actually have to use a SysScanDesc on AttributeRelationId) and look
> for atts that have the type I’m interested in, and if I find one, yay,
> we have a winner.

Depending on what you do, it might not be a bad idea to do an
index_open() on the oid. The returned Relation entry will already have
looked up the relevant types, and you possibly want to lock anyway...

Greetings,

Andres Freund

Re: Unsigned RPM's ?

[Devrim Gündüz]

Hi,

On Mon, 2017-12-11 at 10:19 +, Patrik Martinsson wrote:

> If I'm not mistaken the latest available rpms for Red Hat 7.3 is missing
> the signature, doing a simple
> 
> $ > rpm -qpi
> https://download.postgresql.org/pub/repos/yum/9.3/redhat/rhel-7.3-x86_64/post
> gresql93-server-9.3.20-3PGDG.rhel7.x86_64.rpm
> "

This was also raised here:

https://redmine.postgresql.org/issues/2942

...and I fixed it today. Sorry for the inconvenience.

Regards,
-- 
Devrim Gündüz
EnterpriseDB: https://www.enterprisedb.com
PostgreSQL Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR

signature.asc
Description: This is a digitally signed message part

Re: How to know if a database has changed

[Sam Gendler]

I think there's a more useful question, which is why do you want to do
this?  If it is just about conditional backups, surely the cost of backup
storage is low enough, even in S3 or the like, that a duplicate backup is
an afterthought from a cost perspective? Before you start jumping through
hoops to make your backups conditional, I'd first do some analysis and
figure out what the real cost of the thing I'm trying to avoid actually is,
since my guess is that you are deep into a premature optimization
 here, where either the cost of
the duplicate backup isn't consequential or the frequency of duplicate
backups is effectively 0.  It would always be possible to run some kind of
checksum on the backup and skip storing it if it matches the previous
backup's checksum if you decide that there truly is value in conditionally
backing up the db.  Sure, that would result in dumping a db that doesn't
need to be dumped, but if your write transaction rate is so low that
backups end up being duplicates on a regular basis, then surely you can
afford the cost of a pg_dump without any significant impact on performance?

On Mon, Dec 11, 2017 at 10:49 AM, Andreas Kretschmer <
andr...@a-kretschmer.de> wrote:

>
>
> Am 11.12.2017 um 18:26 schrieb Andreas Kretschmer:
>
>> it's just a rough idea...
>>
>
> ... and not perfect, because you can't capture ddl in this way.
>
>
>
> Regards, Andreas
>
> --
> 2ndQuadrant - The PostgreSQL Support Company.
> www.2ndQuadrant.com
>
>
>