Re: queriing the version of libpq

[crbenesch]

#include 

/usr/include/pg_config-x86_64.h:#define PG_VERSION "9.6.9"
/usr/include/pg_config-x86_64.h:#define PG_VERSION_NUM 90609
/usr/include/pg_config-x86_64.h:#define PG_VERSION_STR "PostgreSQL 9.6.9 on
x86_64-redhat-linux-gnu, compiled by gcc (GCC) 7.3.1 20180303 (Red Hat
7.3.1-5), 64-bit"

I also have source for 8.4 and its there too.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Catching unique_violation exception on specific column/index

[Alexey Dokuchaev]

Hi there,

I have a table with several UNIQUE and CHECK constraints.  One of these
UNIQUE constraints actually *can* be violated -- not on the table level,
of course, but on the application level -- meaning, if the entry with
particular foo_key is already in there, do not throw an exception, just
silently do nothing.

The usual approach ("EXCEPTION WHEN unique_violation THEN ... END") does
not really cut it because I want to catch unique_violation only when it
happens on "foo_key", and still rightfully complain on others.  However,
there is no "EXCEPTION WHEN unique_violation ON (foo_key)" or something.
Is there a way to do this without using triggers and in a less ugly way
than the code below?

IF SQLERRM = 'duplicate key value violates unique constraint' ||
  ' "foo_key"' THEN
RAISE NOTICE '%: %', SQLSTATE, SQLERRM;
ELSE
RAISE EXCEPTION '%: %', SQLSTATE, SQLERRM;
END IF;

./danfe

Re: Catching unique_violation exception on specific column/index

[Alexey Dokuchaev]

On Mon, Jun 11, 2018 at 05:10:33PM +0700, Alexey Dokuchaev wrote:
> The usual approach ("EXCEPTION WHEN unique_violation THEN ... END") does
> not really cut it because I want to catch unique_violation only when it
> happens on "foo_key", and still rightfully complain on others.  However,
> there is no "EXCEPTION WHEN unique_violation ON (foo_key)" or something.
> Is there a way to do this without using triggers and in a less ugly way
> than the code below?

Oh, I completely forgot about rules:

CREATE OR REPLACE RULE ignore_duplicate_foo AS
  ON INSERT TO table_bar WHERE EXISTS (
SELECT 1 FROM table_bar WHERE foo = NEW.foo)
  DO INSTEAD NOTHING;

Sorry for the noise.

./danfe

Re: Catching unique_violation exception on specific column/index

[Thomas Kellerer]

Alexey Dokuchaev schrieb am 11.06.2018 um 12:10:
> I have a table with several UNIQUE and CHECK constraints.  One of these
> UNIQUE constraints actually *can* be violated -- not on the table level,
> of course, but on the application level -- meaning, if the entry with
> particular foo_key is already in there, do not throw an exception, just
> silently do nothing.
> 
> The usual approach ("EXCEPTION WHEN unique_violation THEN ... END") does
> not really cut it because I want to catch unique_violation only when it
> happens on "foo_key", and still rightfully complain on others.  However,
> there is no "EXCEPTION WHEN unique_violation ON (foo_key)" or something.
> Is there a way to do this without using triggers and in a less ugly way
> than the code below?
> 
> IF SQLERRM = 'duplicate key value violates unique constraint' ||
>   ' "foo_key"' THEN
> RAISE NOTICE '%: %', SQLSTATE, SQLERRM;
> ELSE
> RAISE EXCEPTION '%: %', SQLSTATE, SQLERRM;
> END IF;
> 
> ./danfe
 
What's wrong  with: 

  INSERT ...
  ON CONFLICT (foo_key) DO NOTHING


 

Re: Catching unique_violation exception on specific column/index

[Alexey Dokuchaev]

On Mon, Jun 11, 2018 at 12:30:13PM +0200, Thomas Kellerer wrote:
> Alexey Dokuchaev schrieb am 11.06.2018 um 12:10:
> > I have a table with several UNIQUE and CHECK constraints.  One of these
> > UNIQUE constraints actually *can* be violated -- not on the table level,
> > of course, but on the application level -- meaning, if the entry with
> > particular foo_key is already in there, do not throw an exception, just
> > silently do nothing.
>  
> What's wrong with:
> 
>   INSERT ...
>   ON CONFLICT (foo_key) DO NOTHING

Nothing I guess, except that it is available since 9.5 (right?), and I try
to stay compatible with 9.3.  Sorry for not saying this in the first place.

./danfe

Re: Catching unique_violation exception on specific column/index

[Andreas Kretschmer]

Am 11.06.2018 um 12:58 schrieb Alexey Dokuchaev:

What's wrong with:

   INSERT ...
   ON CONFLICT (foo_key) DO NOTHING

Nothing I guess, except that it is available since 9.5 (right?), and I try
to stay compatible with 9.3.  Sorry for not saying this in the first place.

./danfe



... 9.3 will be out of support soon


Regards, Andreas

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

Re: Catching unique_violation exception on specific column/index

[Thomas Kellerer]

Alexey Dokuchaev schrieb am 11.06.2018 um 12:58:
>>> I have a table with several UNIQUE and CHECK constraints.  One of these
>>> UNIQUE constraints actually *can* be violated -- not on the table level,
>>> of course, but on the application level -- meaning, if the entry with
>>> particular foo_key is already in there, do not throw an exception, just
>>> silently do nothing.
>>  
>> What's wrong with:
>>
>>   INSERT ...
>>   ON CONFLICT (foo_key) DO NOTHING
> 
> Nothing I guess, except that it is available since 9.5 (right?), and I try
> to stay compatible with 9.3.  Sorry for not saying this in the first place.

Andreas already mentioned that 9.3 will be EOL soon (3 months from now), but 
the performance and efficiency for concurrent execution of ON CONFLICT is 
much better than anything you can implement yourself. 

If that functionality is an important part of your code, you should consider 
upgrading to 10 (or 9.6 if your are really conservative) rather sooner
than later. 

Re: Service pgpool

[Jean Claude]

Hi,

Thanks, I have reinstall the package and now it's OK.

Regards,


2018-06-10 3:55 GMT+02:00 Bo Peng :

> Hi,
>
> > Jun 08 04:40:17 asa-pgpool02 pgpool-II-10[30787]: Starting pgpool-II-10
> > service: pgpool is already running with pid 4285 [FAILED]
>
> It seemed like Pgpool-II is already started. Did you start Pgpool-II twice?
>
> Also may I ask some questions?
>
> - How did you install Pgpool-II, using source code or RPM packages?
>
> - If you installed Pgpool-II by using RPM packages, did you use the RPM
> packages
>   from PGDG repository provided by PostgreSQL community or from  pgpool
> yum repository
>   provided by Pgpool-II community?
>
> - Could you provide the RPM package name?
>
> - How did you start Pgpool-II?
>   It seemed like you started Pgpool-II with systemd, but the init script
> is used.
>   Normally on RHEL 7.x the systemd service is used to start a service.
>
>
> On Fri, 8 Jun 2018 10:47:34 +0200
> Jean Claude  wrote:
>
> > Hi Bo Peng,
> >
> > Thank you for your feedback.
> >
> > You can find the status of my service below :
> >
> > [root@asa-pgpool02 tmp]# systemctl status pgpool-II-10.service
> > ● pgpool-II-10.service - SYSV: Starts and stops the pgpool daemon
> >Loaded: loaded (/etc/rc.d/init.d/pgpool-II-10; bad; vendor preset:
> > disabled)
> >Active: failed (Result: exit-code) since Fri 2018-06-08 04:40:17 EDT;
> > 5min ago
> >  Docs: man:systemd-sysv-generator(8)
> >   Process: 30787 ExecStart=/etc/rc.d/init.d/pgpool-II-10 start
> > (code=exited, status=1/FAILURE)
> >
> > Jun 08 04:40:17 asa-pgpool02 systemd[1]: Starting SYSV: Starts and stops
> > the pgpool daemon...
> > Jun 08 04:40:17 asa-pgpool02 pgpool-II-10[30787]: Starting pgpool-II-10
> > service: pgpool is already running with pid 4285 [FAILED]
> > Jun 08 04:40:17 asa-pgpool02 systemd[1]: pgpool-II-10.service: control
> > process exited, code=exited status=1
> > Jun 08 04:40:17 asa-pgpool02 systemd[1]: Failed to start SYSV: Starts and
> > stops the pgpool daemon.
> > Jun 08 04:40:17 asa-pgpool02 systemd[1]: Unit pgpool-II-10.service
> entered
> > failed state.
> > Jun 08 04:40:17 asa-pgpool02 systemd[1]: pgpool-II-10.service failed.
> > [root@asa-pgpool02 tmp]#
> >
> > Thanks
> >
> >
> >
> > 2018-06-08 4:22 GMT+02:00 Bo Peng :
> >
> > > Hi,
> > >
> > > > below my problem about daemon :
> > > >
> > > > Jun 07 14:06:45 vm02 systemd[1]: Failed to start SYSV: Starts and
> stops
> > > the pgpool daemon.
> > > > Jun 07 14:06:45 vm02 systemd[1]: Unit pgpool-II-10.service entered
> > > failed state.
> > > > Jun 07 14:06:45 vm02 systemd[1]: pgpool-II-10.service failed.
> > >
> > > Could you show more error messages about systemd?
> > >
> > > As a common case, starting Pgpool-II failed due to
> > > missing the socket file directory which is specified
> > > in pgpool.conf.
> > >
> > > By the way as Joshua has already suggested, probably It
> > > would better to make questions in the Pgpool-II dedicated
> > > mailing list.
> > >
> > > On Thu, 7 Jun 2018 20:16:19 +0200
> > > Jean Claude  wrote:
> > >
> > > > Hi all,
> > > >
> > > > below my problem about daemon :
> > > >
> > > > Jun 07 14:06:45 vm02 systemd[1]: Failed to start SYSV: Starts and
> stops
> > > the
> > > > pgpool daemon.
> > > > Jun 07 14:06:45 vm02 systemd[1]: Unit pgpool-II-10.service entered
> failed
> > > > state.
> > > > Jun 07 14:06:45 vm02 systemd[1]: pgpool-II-10.service failed.
> > > >
> > > > Can you help me?
> > > >
> > > > Thanks a lot
> > >
> > >
> > > --
> > > Bo Peng 
> > > SRA OSS, Inc. Japan
> > >
> > >
>
>
> --
> Bo Peng 
> SRA OSS, Inc. Japan
>
>

Add to watchdog cluster request is rejected by node

[Jean Claude]

Hi,

The following error is a bug ?

Jun 11 08:57:04 asa-pgpool02 pgpool[3240]: [1-1] 2018-06-11 08:57:04: pid
3240: WARNING:  checking setuid bit of if_up_cmd
Jun 11 08:57:04 asa-pgpool02 pgpool[3240]: [1-2] 2018-06-11 08:57:04: pid
3240: DETAIL:  ifup[/sbin/ip] doesn't have setuid bit
Jun 11 08:57:04 asa-pgpool02 pgpool[3240]: [2-1] 2018-06-11 08:57:04: pid
3240: WARNING:  checking setuid bit of if_down_cmd
Jun 11 08:57:04 asa-pgpool02 pgpool[3240]: [2-2] 2018-06-11 08:57:04: pid
3240: DETAIL:  ifdown[/sbin/ip] doesn't have setuid bit
Jun 11 08:57:04 asa-pgpool02 pgpool[3240]: [3-1] 2018-06-11 08:57:04: pid
3240: LOG:  waiting for watchdog to initialize
Jun 11 08:57:04 asa-pgpool02 pgpool[3241]: [3-1] 2018-06-11 08:57:04: pid
3241: LOG:  setting the local watchdog node name to "
asa-pgpool02.adm.cacc.ch:5432 Linux asa-pgpool02"
Jun 11 08:57:04 asa-pgpool02 pgpool[3241]: [4-1] 2018-06-11 08:57:04: pid
3241: LOG:  watchdog cluster is configured with 1 remote nodes
Jun 11 08:57:04 asa-pgpool02 pgpool[3241]: [5-1] 2018-06-11 08:57:04: pid
3241: LOG:  watchdog remote node:0 on asa-pgpool01.adm.cacc.ch:9000
Jun 11 08:57:04 asa-pgpool02 pgpool[3241]: [6-1] 2018-06-11 08:57:04: pid
3241: LOG:  interface monitoring is disabled in watchdog
Jun 11 08:57:04 asa-pgpool02 pgpool[3241]: [7-1] 2018-06-11 08:57:04: pid
3241: LOG:  watchdog node state changed from [DEAD] to [LOADING]
Jun 11 08:57:04 asa-pgpool02 pgpool[3241]: [8-1] 2018-06-11 08:57:04: pid
3241: LOG:  new outbound connection to asa-pgpool01.adm.cacc.ch:9000
Jun 11 08:57:04 asa-pgpool02 pgpool[3241]: [9-1] 2018-06-11 08:57:04: pid
3241: *FATAL:  Add to watchdog cluster request is rejected by node
"asa-pgpool01.adm.cacc.ch:9000 "*

pgpool-II version :
pgpool-II version 3.7.3 (amefuriboshi)

Thanks.

Re: pg_upgrade and wraparound

[Adrian Klaver]

On 06/10/2018 11:46 PM, Alexander Shutyaev wrote:
 >> Is this the regular Postgres log or the pg_upgrade log which should 
be something like pg_upgrade_server.log?


This is the pg_upgrade_dump_16400.log.

How did you get into the 10 cluster to report on the database OID's and 

names?

After the pg_upgrade failed I was able to start both clusters, so I 
connected to the new 10.4 cluster and ran the query.


I am at a loss for an explanation. My thoughts:

1) The database with an  OID of 0 is a mystery, though it does not seem 
to be stopping the upgrade by itself.


2) The upgrade stops because of transaction ID wraparound, which is 
strange as that is not showing up in the 9.6 cluster I presume. You 
might want the queries found below on the 9.6 and 10 clusters to help 
figure this out:


https://www.postgresql.org/docs/10/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

SELECT c.oid::regclass as table_name,
   greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm');

SELECT datname, age(datfrozenxid) FROM pg_database;




Which database has the large objects?


bof (OID=16400). It is also effectively the only database that matters 
here. The other one - sslentry only contains a couple of tables and a 
dozen of records.



Did you check this view to confirm?


Yes, I did:

select * from pg_prepared_xacts;
  transaction | gid | prepared | owner | database
-+-+--+---+--
(0 rows)




--
Adrian Klaver
adrian.kla...@aklaver.com

Fwd: Add to watchdog cluster request is rejected by node

[Jean Claude]

Hi all,

How I can resolved this error after restart my pgpool-II standby ?


Jun 11 08:57:04 asa-pgpool02 pgpool[3240]: [1-1] 2018-06-11 08:57:04: pid
3240: WARNING:  checking setuid bit of if_up_cmd
Jun 11 08:57:04 asa-pgpool02 pgpool[3240]: [1-2] 2018-06-11 08:57:04: pid
3240: DETAIL:  ifup[/sbin/ip] doesn't have setuid bit
Jun 11 08:57:04 asa-pgpool02 pgpool[3240]: [2-1] 2018-06-11 08:57:04: pid
3240: WARNING:  checking setuid bit of if_down_cmd
Jun 11 08:57:04 asa-pgpool02 pgpool[3240]: [2-2] 2018-06-11 08:57:04: pid
3240: DETAIL:  ifdown[/sbin/ip] doesn't have setuid bit
Jun 11 08:57:04 asa-pgpool02 pgpool[3240]: [3-1] 2018-06-11 08:57:04: pid
3240: LOG:  waiting for watchdog to initialize
Jun 11 08:57:04 asa-pgpool02 pgpool[3241]: [3-1] 2018-06-11 08:57:04: pid
3241: LOG:  setting the local watchdog node name to "
asa-pgpool02.adm.cacc.ch:5432 Linux asa-pgpool02"
Jun 11 08:57:04 asa-pgpool02 pgpool[3241]: [4-1] 2018-06-11 08:57:04: pid
3241: LOG:  watchdog cluster is configured with 1 remote nodes
Jun 11 08:57:04 asa-pgpool02 pgpool[3241]: [5-1] 2018-06-11 08:57:04: pid
3241: LOG:  watchdog remote node:0 on asa-pgpool01.adm.cacc.ch:9000
Jun 11 08:57:04 asa-pgpool02 pgpool[3241]: [6-1] 2018-06-11 08:57:04: pid
3241: LOG:  interface monitoring is disabled in watchdog
Jun 11 08:57:04 asa-pgpool02 pgpool[3241]: [7-1] 2018-06-11 08:57:04: pid
3241: LOG:  watchdog node state changed from [DEAD] to [LOADING]
Jun 11 08:57:04 asa-pgpool02 pgpool[3241]: [8-1] 2018-06-11 08:57:04: pid
3241: LOG:  new outbound connection to asa-pgpool01.adm.cacc.ch:9000
Jun 11 08:57:04 asa-pgpool02 pgpool[3241]: [9-1] 2018-06-11 08:57:04: pid
3241: *FATAL:  Add to watchdog cluster request is rejected by node
"asa-pgpool01.adm.cacc.ch:9000 "*

pgpool-II version :
pgpool-II version 3.7.3 (amefuriboshi)

Thanks.

ALTER TABLE SET (toast.asdf) is not rejected

[Justin Pryzby]

I noticed that this is accepted:

postgres=# ALTER TABLE t SET (toast.asdf=128);
ALTER TABLE

I thought since "toast" was a core namespace, it would've been rejected?

I recall having read a discussion about verifying these ... I wasn't able
to find what I was thinking of, but found this one.
https://www.postgresql.org/message-id/flat/20090114144332.GF24156%40alvh.no-ip.org

Justin

Re: ALTER TABLE SET (toast.asdf) is not rejected

[Alvaro Herrera]

On 2018-Jun-11, Justin Pryzby wrote:

> I noticed that this is accepted:
> 
> postgres=# ALTER TABLE t SET (toast.asdf=128);
> ALTER TABLE
> 
> I thought since "toast" was a core namespace, it would've been rejected?
> 
> I recall having read a discussion about verifying these ... I wasn't able
> to find what I was thinking of, but found this one.
> https://www.postgresql.org/message-id/flat/20090114144332.GF24156%40alvh.no-ip.org

Uh.  ISTM that this was lost during the 9.4 cycle, because that command *is*
rejected in 9.3.

alvherre=# ALTER TABLE t SET (toast.asdf=128);
ERROR:  unrecognized parameter "asdf"

but 9.4 takes it.  Weird.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Fwd: Add to watchdog cluster request is rejected by node

[Adrian Klaver]

On 06/11/2018 06:15 AM, Jean Claude wrote:

Hi all,

How I can resolved this error after restart my pgpool-II standby ?


Jun 11 08:57:04 asa-pgpool02 pgpool[3240]: [1-1] 2018-06-11 08:57:04: 
pid 3240: WARNING:  checking setuid bit of if_up_cmd
Jun 11 08:57:04 asa-pgpool02 pgpool[3240]: [1-2] 2018-06-11 08:57:04: 
pid 3240: DETAIL:  ifup[/sbin/ip] doesn't have setuid bit
Jun 11 08:57:04 asa-pgpool02 pgpool[3240]: [2-1] 2018-06-11 08:57:04: 
pid 3240: WARNING:  checking setuid bit of if_down_cmd
Jun 11 08:57:04 asa-pgpool02 pgpool[3240]: [2-2] 2018-06-11 08:57:04: 
pid 3240: DETAIL:  ifdown[/sbin/ip] doesn't have setuid bit
Jun 11 08:57:04 asa-pgpool02 pgpool[3240]: [3-1] 2018-06-11 08:57:04: 
pid 3240: LOG:  waiting for watchdog to initialize
Jun 11 08:57:04 asa-pgpool02 pgpool[3241]: [3-1] 2018-06-11 08:57:04: 
pid 3241: LOG:  setting the local watchdog node name to 
"asa-pgpool02.adm.cacc.ch:5432  
Linux asa-pgpool02"
Jun 11 08:57:04 asa-pgpool02 pgpool[3241]: [4-1] 2018-06-11 08:57:04: 
pid 3241: LOG:  watchdog cluster is configured with 1 remote nodes
Jun 11 08:57:04 asa-pgpool02 pgpool[3241]: [5-1] 2018-06-11 08:57:04: 
pid 3241: LOG:  watchdog remote node:0 on asa-pgpool01.adm.cacc.ch:9000 

Jun 11 08:57:04 asa-pgpool02 pgpool[3241]: [6-1] 2018-06-11 08:57:04: 
pid 3241: LOG:  interface monitoring is disabled in watchdog
Jun 11 08:57:04 asa-pgpool02 pgpool[3241]: [7-1] 2018-06-11 08:57:04: 
pid 3241: LOG:  watchdog node state changed from [DEAD] to [LOADING]
Jun 11 08:57:04 asa-pgpool02 pgpool[3241]: [8-1] 2018-06-11 08:57:04: 
pid 3241: LOG:  new outbound connection to asa-pgpool01.adm.cacc.ch:9000 

Jun 11 08:57:04 asa-pgpool02 pgpool[3241]: [9-1] 2018-06-11 08:57:04: 
pid 3241: *FATAL:  Add to watchdog cluster request is rejected by node 
"asa-pgpool01.adm.cacc.ch:9000 "*


I don't use pgpool, but looks to me like asa-pgpool01.adm.cacc.ch is not 
set up for watchdog mode.


Have you looked at this:

http://www.pgpool.net/docs/latest/en/html/example-watchdog.html

If that does not help then I would suggest asking here:

https://www.pgpool.net/mailman/listinfo/pgpool-general


*
*
pgpool-II version :*
*pgpool-II version 3.7.3 (amefuriboshi)*

*
Thanks.




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Sort is generating rows

[Adrian Klaver]

On 06/10/2018 11:47 PM, Nicolas Seinlet wrote:

Hi,

a currency rate can have no company, and is then applicable to 
currencies which have no rate specific for the company.




I see. So what happens if, for testing purposes, you eliminate the

r.company_id IS NULL OR

part?



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: pg_upgrade and wraparound

[Andres Freund]

Hi,

On 2018-06-09 13:46:16 +0300, Alexander Shutyaev wrote:
> Hello!
> 
> I've been trying to upgrade a postgresql cluster from 9.6 to 10. I've
> executed the pg_upgrade with the following options:
> 
>  /usr/lib/postgresql/10/bin/pg_upgrade -b /usr/lib/postgresql/9.6/bin/ -B
> /usr/lib/postgresql/10/bin/ -d /var/lib/postgresql/9.6/main -D
> /var/lib/postgresql/10/main -o ' -c
> config_file=/etc/postgresql/9.6/main/postgresql.conf' -O ' -c
> config_file=/etc/postgresql/10/main/postgresql.conf'
> 
> The upgrade operation failed after several hours with the following error:
> 
> database is not accepting commands to avoid wraparound data loss in
> database with OID 0
> 
> Earlier in the log there are a lot of messages like
> 
> pg_restore: executing BLOB 1740736966
> pg_restore: WARNING:  database with OID 0 must be vacuumed within 1000279
> transactions
> HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that
> database.
> You might also need to commit or roll back old prepared transactions.

Since this happens in the version you're migrating too, I suggest
replacing the warning with a PANIC and then using the debugger to look
at the corefile generated (when using an appropriate ulimit).  It's not
immediately obvious why there'd not be correct knowledge about the
oldest database around. If you do so, please include 'p *ShmemVariableCache'
output.

Greetings,

Andres Freund

Re: pg_upgrade and wraparound

[Andres Freund]

On 2018-06-09 15:52:26 -0400, Tom Lane wrote:
> Adrian Klaver  writes:
> > On 06/09/2018 03:46 AM, Alexander Shutyaev wrote:
> >> The upgrade operation failed after several hours with the following error:
> >> database is not accepting commands to avoid wraparound data loss in 
> >> database with OID 0
> 
> > Do you know which database has an OID of 0?
> 
> Well, none do, so the correct question is what is passing an invalid
> database OID to the code that's complaining.  This sure looks like a
> bug, though I'm not sure we have enough info to locate it.

It sure looks like ShmemVariableCache->oldestXidDB isn't initialized.
As far as I remember we just initialize that from a checkpoint at
startup.   I suspect the issue is that pg_resetwal does:
if (set_xid != 0)
{
ControlFile.checkPointCopy.nextXid = set_xid;

/*
 * For the moment, just set oldestXid to a value that will force
 * immediate autovacuum-for-wraparound.  It's not clear whether 
adding
 * user control of this is useful, so let's just do something 
that's
 * reasonably safe.  The magic constant here corresponds to the
 * maximum allowed value of autovacuum_freeze_max_age.
 */
ControlFile.checkPointCopy.oldestXid = set_xid - 20;
if (ControlFile.checkPointCopy.oldestXid < 
FirstNormalTransactionId)
ControlFile.checkPointCopy.oldestXid += 
FirstNormalTransactionId;
ControlFile.checkPointCopy.oldestXidDB = InvalidOid;
}

but we have codepath that doesn't check for oldestXidDB being
InvalidOid.  Not great.

Greetings,

Andres Freund

Re: pg_upgrade and wraparound

[Tom Lane]

Andres Freund  writes:
> I suspect the issue is that pg_resetwal does:
>   if (set_xid != 0)
>   {
>   ControlFile.checkPointCopy.nextXid = set_xid;

>   /*
>* For the moment, just set oldestXid to a value that will force
>* immediate autovacuum-for-wraparound.  It's not clear whether 
> adding
>* user control of this is useful, so let's just do something 
> that's
>* reasonably safe.  The magic constant here corresponds to the
>* maximum allowed value of autovacuum_freeze_max_age.
>*/
>   ControlFile.checkPointCopy.oldestXid = set_xid - 20;
>   if (ControlFile.checkPointCopy.oldestXid < 
> FirstNormalTransactionId)
>   ControlFile.checkPointCopy.oldestXid += 
> FirstNormalTransactionId;
>   ControlFile.checkPointCopy.oldestXidDB = InvalidOid;
>   }

> but we have codepath that doesn't check for oldestXidDB being
> InvalidOid.  Not great.

Hm, I think I'd define the problem as "pg_resetwal is violating the
expectation that oldestXidDB be valid".

However, this just explains the basically-cosmetic issue that the
complaint message mentions OID 0.  It doesn't really get us to the
answer to why Alexander is seeing a failure.  It might be useful
to see pg_controldata output for the old cluster, as well as
"select datname, datfrozenxid from pg_database" output from the
old cluster.

regards, tom lane

Re: pg_upgrade and wraparound

[Andres Freund]

On 2018-06-11 13:14:12 -0400, Tom Lane wrote:
> Andres Freund  writes:
> > I suspect the issue is that pg_resetwal does:
> > if (set_xid != 0)
> > {
> > ControlFile.checkPointCopy.nextXid = set_xid;
> 
> > /*
> >  * For the moment, just set oldestXid to a value that will force
> >  * immediate autovacuum-for-wraparound.  It's not clear whether 
> > adding
> >  * user control of this is useful, so let's just do something 
> > that's
> >  * reasonably safe.  The magic constant here corresponds to the
> >  * maximum allowed value of autovacuum_freeze_max_age.
> >  */
> > ControlFile.checkPointCopy.oldestXid = set_xid - 20;
> > if (ControlFile.checkPointCopy.oldestXid < 
> > FirstNormalTransactionId)
> > ControlFile.checkPointCopy.oldestXid += 
> > FirstNormalTransactionId;
> > ControlFile.checkPointCopy.oldestXidDB = InvalidOid;
> > }
> 
> > but we have codepath that doesn't check for oldestXidDB being
> > InvalidOid.  Not great.
> 
> Hm, I think I'd define the problem as "pg_resetwal is violating the
> expectation that oldestXidDB be valid".

Well, what could it do otherwise?  ForceTransactionIdLimitUpdate()
currently does a syscache check for database existence. That'll just
return a lookup failure for InvalidOid, so we're reasonably good on that
front.

Using a hardcoded 20 seems worse, will have funny results if
running with a smaller autovacuum_freeze_max_age...


> However, this just explains the basically-cosmetic issue that the
> complaint message mentions OID 0.  It doesn't really get us to the
> answer to why Alexander is seeing a failure.  It might be useful
> to see pg_controldata output for the old cluster, as well as
> "select datname, datfrozenxid from pg_database" output from the
> old cluster.

pg_upgrade starts the server with autovacuum disabled, I suspect
restoring all the large objects ends up using a lot of transaction
ids. GetNewTransactionId() should start autovacuum, but I'd guess that's
where things are going wrong for some reason.

Alexander, could you hack things up so autovacuum logging is enabled
(log_autovacuum_min_duration=0), and see whether it's triggered?

I'm not entirely clear why pg_restore appears to use a separate
transaction for each large object, surely exascerbating the problem.

Greetings,

Andres Freund

Re: Catching unique_violation exception on specific column/index

[Alexey Dokuchaev]

On Mon, Jun 11, 2018 at 01:26:16PM +0200, Thomas Kellerer wrote:
> If that functionality is an important part of your code, you should
> consider upgrading to 10 (or 9.6 if your are really conservative)
> rather sooner than later.

Oh well, fair enough.  As much as I'd love to stick to the lowest
supported (and sometimes even unsupported) versions, ON CONFLICT is
indeed very handy, esp. since I have a few UPSERT's implemented the
old way already (via catching the "unique_violation" exception).

Shall I update to 9.6/10, I have a bit off-topic (to the original
subject) question: right now, when I need to get the length of an
array (never multidimensional), I do this:

coalesce(array_length(foo, 1), 0);

In 9.4+, I can call cardinality().  I'm a bit hesitant: is doing so
semantically correct, or should I still do coalesce(..., 0) in this
case?  This is not about the outcome, it's whether cardinality() is
semantically correct to obtain the number of the array items, or it
was introduced for other means?

./danfe

Re: Catching unique_violation exception on specific column/index

[Adrian Klaver]

On 06/11/2018 11:21 AM, Alexey Dokuchaev wrote:

On Mon, Jun 11, 2018 at 01:26:16PM +0200, Thomas Kellerer wrote:

If that functionality is an important part of your code, you should
consider upgrading to 10 (or 9.6 if your are really conservative)
rather sooner than later.


Oh well, fair enough.  As much as I'd love to stick to the lowest
supported (and sometimes even unsupported) versions, ON CONFLICT is
indeed very handy, esp. since I have a few UPSERT's implemented the
old way already (via catching the "unique_violation" exception).

Shall I update to 9.6/10, I have a bit off-topic (to the original
subject) question: right now, when I need to get the length of an
array (never multidimensional), I do this:

 coalesce(array_length(foo, 1), 0);

In 9.4+, I can call cardinality().  I'm a bit hesitant: is doing so
semantically correct, or should I still do coalesce(..., 0) in this
case?  This is not about the outcome, it's whether cardinality() is
semantically correct to obtain the number of the array items, or it
was introduced for other means?


https://www.postgresql.org/docs/10/static/arrays.html

8.15.3. Accessing Arrays

"... cardinality returns the total number of elements in an array across 
all dimensions. It is effectively the number of rows a call to unnest 
would yield: ..."





./danfe




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: pg_upgrade and wraparound

[Alexander Shutyaev]

I'm back with more details.

First, I've deleted the smaller sslentry database, since I don't need it,
just so that it doesn't somehow spoil the picture. Now there is only 1 user
database - bof (OID=16400). After that I've ran the pg_upgrade on a clean
10.4 cluster and it failed in the same way.

Now, the answers to your queries.

>> 2) The upgrade stops because of transaction ID wraparound, which is
strange as that is not showing up in the 9.6 cluster I presume. You might
want the queries found below on the 9.6 and 10 clusters to help figure this
out:

>> SELECT c.oid::regclass as table_name,
>>greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
>> FROM pg_class c
>> LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
>> WHERE c.relkind IN ('r', 'm');

9.6 result - see attached 96-query1.txt
10.4 result - see attached 104-query1.txt

>> SELECT datname, age(datfrozenxid) FROM pg_database;

9.6 result - see attached 96-query2.txt
10.4 result - see attached 104-query2.txt

>>  It might be useful
>> to see pg_controldata output for the old cluster, as well as
>> "select datname, datfrozenxid from pg_database" output from the
>> old cluster.

for the query - see above, for pg_controldata:

9.6 - see attached 96-pg_controldata.txt
10.4 - see attached 104-pg_controldata.txt

>> Alexander, could you hack things up so autovacuum logging is enabled
>> (log_autovacuum_min_duration=0), and see whether it's triggered?

I'll be happy to, but that will require to run pg_upgrade once more and
that takes more that half a day and during this time clusters are not
available to me. Given the data I'm attaching it may happen that the
colleagues will want to see something else from my clusters or maybe change
some settings before running the pg_upgrade again. Therefore, I'll wait 12
hours after this message in case there will be any more requests and the
I'll run the pg_upgrade again.

Thank you all for trying to solve this matter, this is much appreciated! :)

2018-06-11 20:29 GMT+03:00 Andres Freund :

> On 2018-06-11 13:14:12 -0400, Tom Lane wrote:
> > Andres Freund  writes:
> > > I suspect the issue is that pg_resetwal does:
> > > if (set_xid != 0)
> > > {
> > > ControlFile.checkPointCopy.nextXid = set_xid;
> >
> > > /*
> > >  * For the moment, just set oldestXid to a value that will
> force
> > >  * immediate autovacuum-for-wraparound.  It's not clear
> whether adding
> > >  * user control of this is useful, so let's just do
> something that's
> > >  * reasonably safe.  The magic constant here corresponds
> to the
> > >  * maximum allowed value of autovacuum_freeze_max_age.
> > >  */
> > > ControlFile.checkPointCopy.oldestXid = set_xid -
> 20;
> > > if (ControlFile.checkPointCopy.oldestXid <
> FirstNormalTransactionId)
> > > ControlFile.checkPointCopy.oldestXid +=
> FirstNormalTransactionId;
> > > ControlFile.checkPointCopy.oldestXidDB = InvalidOid;
> > > }
> >
> > > but we have codepath that doesn't check for oldestXidDB being
> > > InvalidOid.  Not great.
> >
> > Hm, I think I'd define the problem as "pg_resetwal is violating the
> > expectation that oldestXidDB be valid".
>
> Well, what could it do otherwise?  ForceTransactionIdLimitUpdate()
> currently does a syscache check for database existence. That'll just
> return a lookup failure for InvalidOid, so we're reasonably good on that
> front.
>
> Using a hardcoded 20 seems worse, will have funny results if
> running with a smaller autovacuum_freeze_max_age...
>
>
> > However, this just explains the basically-cosmetic issue that the
> > complaint message mentions OID 0.  It doesn't really get us to the
> > answer to why Alexander is seeing a failure.  It might be useful
> > to see pg_controldata output for the old cluster, as well as
> > "select datname, datfrozenxid from pg_database" output from the
> > old cluster.
>
> pg_upgrade starts the server with autovacuum disabled, I suspect
> restoring all the large objects ends up using a lot of transaction
> ids. GetNewTransactionId() should start autovacuum, but I'd guess that's
> where things are going wrong for some reason.
>
> Alexander, could you hack things up so autovacuum logging is enabled
> (log_autovacuum_min_duration=0), and see whether it's triggered?
>
> I'm not entirely clear why pg_restore appears to use a separate
> transaction for each large object, surely exascerbating the problem.
>
> Greetings,
>
> Andres Freund
>
pg_control version number:960
Catalog version number:   201608131
Database system identifier:   6341258630649216079
Database cluster state:   in production
pg_control last modified: Mon 11 Jun 2018 09:10:28 PM MSK
Latest checkpoint location:   811B/24CA0B40
Prior checkpoint location:811B/24CA0AD0
Latest checkpoint's REDO location:  

Re: pg_upgrade and wraparound

[Adrian Klaver]

On 06/11/2018 11:32 AM, Alexander Shutyaev wrote:

I'm back with more details.

First, I've deleted the smaller sslentry database, since I don't need 
it, just so that it doesn't somehow spoil the picture. Now there is only 
1 user database - bof (OID=16400). After that I've ran the pg_upgrade on 
a clean 10.4 cluster and it failed in the same way.


Now, the answers to your queries.

 >> 2) The upgrade stops because of transaction ID wraparound, which is 
strange as that is not showing up in the 9.6 cluster I presume. You 
might want the queries found below on the 9.6 and 10 clusters to help 
figure this out:


 >> SELECT c.oid::regclass as table_name,
 >>        greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
 >> FROM pg_class c
 >> LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
 >> WHERE c.relkind IN ('r', 'm');

9.6 result - see attached 96-query1.txt
10.4 result - see attached 104-query1.txt


I should have been clearer in my previous post, the above query is per 
database. From the query below I am going to say the above query was 
done on the bof database. Is that correct?


Given the below from 96-query2.txt:

template0 | 110588398

Can you run the table_name query in template0 in the 9.6 cluster?




 >> SELECT datname, age(datfrozenxid) FROM pg_database;

9.6 result - see attached 96-query2.txt
10.4 result - see attached 104-query2.txt

 >>  It might be useful
 >> to see pg_controldata output for the old cluster, as well as
 >> "select datname, datfrozenxid from pg_database" output from the
 >> old cluster.

for the query - see above, for pg_controldata:

9.6 - see attached 96-pg_controldata.txt
10.4 - see attached 104-pg_controldata.txt

 >> Alexander, could you hack things up so autovacuum logging is enabled
 >> (log_autovacuum_min_duration=0), and see whether it's triggered?

I'll be happy to, but that will require to run pg_upgrade once more and 
that takes more that half a day and during this time clusters are not 
available to me. Given the data I'm attaching it may happen that the 
colleagues will want to see something else from my clusters or maybe 
change some settings before running the pg_upgrade again. Therefore, 
I'll wait 12 hours after this message in case there will be any more 
requests and the I'll run the pg_upgrade again.


Thank you all for trying to solve this matter, this is much appreciated! :)

2018-06-11 20:29 GMT+03:00 Andres Freund >:


On 2018-06-11 13:14:12 -0400, Tom Lane wrote:
> Andres Freund mailto:and...@anarazel.de>> writes:
> > I suspect the issue is that pg_resetwal does:
> >     if (set_xid != 0)
> >     {
> >             ControlFile.checkPointCopy.nextXid = set_xid;
> 
> >             /*

> >              * For the moment, just set oldestXid to a value that will 
force
> >              * immediate autovacuum-for-wraparound.  It's not clear 
whether adding
> >              * user control of this is useful, so let's just do 
something that's
> >              * reasonably safe.  The magic constant here corresponds to 
the
> >              * maximum allowed value of autovacuum_freeze_max_age.
> >              */
> >             ControlFile.checkPointCopy.oldestXid = set_xid - 20;
> >             if (ControlFile.checkPointCopy.oldestXid < 
FirstNormalTransactionId)
> >                     ControlFile.checkPointCopy.oldestXid += 
FirstNormalTransactionId;
> >             ControlFile.checkPointCopy.oldestXidDB = InvalidOid;
> >     }
> 
> > but we have codepath that doesn't check for oldestXidDB being

> > InvalidOid.  Not great.
> 
> Hm, I think I'd define the problem as "pg_resetwal is violating the

> expectation that oldestXidDB be valid".

Well, what could it do otherwise?  ForceTransactionIdLimitUpdate()
currently does a syscache check for database existence. That'll just
return a lookup failure for InvalidOid, so we're reasonably good on that
front.

Using a hardcoded 20 seems worse, will have funny results if
running with a smaller autovacuum_freeze_max_age...


> However, this just explains the basically-cosmetic issue that the
> complaint message mentions OID 0.  It doesn't really get us to the
> answer to why Alexander is seeing a failure.  It might be useful
> to see pg_controldata output for the old cluster, as well as
> "select datname, datfrozenxid from pg_database" output from the
> old cluster.

pg_upgrade starts the server with autovacuum disabled, I suspect
restoring all the large objects ends up using a lot of transaction
ids. GetNewTransactionId() should start autovacuum, but I'd guess that's
where things are going wrong for some reason.

Alexander, could you hack things up so autovacuum logging is enabled
(log_autovacuum_min_duration=0), and see whether it's triggered?

I'm not entirely clear why pg_restore appears t

Re: pg_upgrade and wraparound

[Alexander Shutyaev]

>>  From the query below I am going to say the above query was done on the
bof database. Is that correct?

Yes, it is.

>>  Can you run the table_name query in template0 in the 9.6 cluster?

At first I couldn't. There was an error:

psql: FATAL:  database "template0" is not currently accepting connections

I've googled for it and found out that template0 is some special system
database that is protected from connections, but that can be changed (see
https://wiki.postgresql.org/wiki/Adventures_in_PostgreSQL,_Episode_1)
I've changed that and connected to this database and ran your query. The
result is attached in 96-query1-template0.txt.

2018-06-11 22:10 GMT+03:00 Adrian Klaver :

> On 06/11/2018 11:32 AM, Alexander Shutyaev wrote:
>
>> I'm back with more details.
>>
>> First, I've deleted the smaller sslentry database, since I don't need it,
>> just so that it doesn't somehow spoil the picture. Now there is only 1 user
>> database - bof (OID=16400). After that I've ran the pg_upgrade on a clean
>> 10.4 cluster and it failed in the same way.
>>
>> Now, the answers to your queries.
>>
>>  >> 2) The upgrade stops because of transaction ID wraparound, which is
>> strange as that is not showing up in the 9.6 cluster I presume. You might
>> want the queries found below on the 9.6 and 10 clusters to help figure this
>> out:
>>
>>  >> SELECT c.oid::regclass as table_name,
>>  >>greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
>>  >> FROM pg_class c
>>  >> LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
>>  >> WHERE c.relkind IN ('r', 'm');
>>
>> 9.6 result - see attached 96-query1.txt
>> 10.4 result - see attached 104-query1.txt
>>
>
> I should have been clearer in my previous post, the above query is per
> database. From the query below I am going to say the above query was done
> on the bof database. Is that correct?
>
> Given the below from 96-query2.txt:
>
> template0 | 110588398
>
> Can you run the table_name query in template0 in the 9.6 cluster?
>
>
>
>>  >> SELECT datname, age(datfrozenxid) FROM pg_database;
>>
>> 9.6 result - see attached 96-query2.txt
>> 10.4 result - see attached 104-query2.txt
>>
>>  >>  It might be useful
>>  >> to see pg_controldata output for the old cluster, as well as
>>  >> "select datname, datfrozenxid from pg_database" output from the
>>  >> old cluster.
>>
>> for the query - see above, for pg_controldata:
>>
>> 9.6 - see attached 96-pg_controldata.txt
>> 10.4 - see attached 104-pg_controldata.txt
>>
>>  >> Alexander, could you hack things up so autovacuum logging is enabled
>>  >> (log_autovacuum_min_duration=0), and see whether it's triggered?
>>
>> I'll be happy to, but that will require to run pg_upgrade once more and
>> that takes more that half a day and during this time clusters are not
>> available to me. Given the data I'm attaching it may happen that the
>> colleagues will want to see something else from my clusters or maybe change
>> some settings before running the pg_upgrade again. Therefore, I'll wait 12
>> hours after this message in case there will be any more requests and the
>> I'll run the pg_upgrade again.
>>
>> Thank you all for trying to solve this matter, this is much appreciated!
>> :)
>>
>> 2018-06-11 20:29 GMT+03:00 Andres Freund > and...@anarazel.de>>:
>>
>> On 2018-06-11 13:14:12 -0400, Tom Lane wrote:
>> > Andres Freund mailto:and...@anarazel.de>>
>> writes:
>> > > I suspect the issue is that pg_resetwal does:
>> > > if (set_xid != 0)
>> > > {
>> > > ControlFile.checkPointCopy.nextXid = set_xid;
>> > > > /*
>> > >  * For the moment, just set oldestXid to a value that
>> will force
>> > >  * immediate autovacuum-for-wraparound.  It's not
>> clear whether adding
>> > >  * user control of this is useful, so let's just do
>> something that's
>> > >  * reasonably safe.  The magic constant here
>> corresponds to the
>> > >  * maximum allowed value of autovacuum_freeze_max_age.
>> > >  */
>> > > ControlFile.checkPointCopy.oldestXid = set_xid -
>> 20;
>> > > if (ControlFile.checkPointCopy.oldestXid <
>> FirstNormalTransactionId)
>> > > ControlFile.checkPointCopy.oldestXid +=
>> FirstNormalTransactionId;
>> > > ControlFile.checkPointCopy.oldestXidDB = InvalidOid;
>> > > }
>> > > > but we have codepath that doesn't check for oldestXidDB
>> being
>> > > InvalidOid.  Not great.
>> > > Hm, I think I'd define the problem as "pg_resetwal is
>> violating the
>> > expectation that oldestXidDB be valid".
>>
>> Well, what could it do otherwise?  ForceTransactionIdLimitUpdate()
>> currently does a syscache check for database existence. That'll just
>> return a lookup failure for InvalidOid, so we're reasonably good on
>> that
>> front.
>>
>> Using a hardcoded 200

Re: ERROR: found multixact from before relminmxid

[Jeremy Finzel]

>
> Is there any chance I could get access
> to a copy of the data? It's very hard to debug something like this
> without something that can reproduce the issue...
>

It would be very difficult for us to be able to clean and anonymize this
data and provide a snapshot publicly.  But I am very willing to hook up a
debugger on a snapshot that reproduces the issue if you can guide me into
what exactly I should look for here.

Thanks,
Jeremy

Re: Multiple PostgreSQL instances on one machine

[Merlin Moncure]

On Fri, Jun 8, 2018 at 3:29 PM Tony Sullivan  wrote:
>
> I am trying to consolidate some machines in my server room particularly in
> the testing environment and I was hoping someone could point me in the
> right direction.
>
> I currently have three machines running PostgreSQL for testing purposes.
> Each week a backup is made of the production database and this is deployed
> onto these three machines. None of these machines is any where near
> capacity. I would like to host all three instances on one machine.
>
> I know that "initdb" can be used to create additional installations, but
> the part I am unsure about is the tablespace. The production database has
> a tablespace defined that resides on its SSD. When I use the production
> backup, I have to create a mount point on the test machine with the same
> name as the one on the production database. I am not certain how I would
> handle this situation if I am serving three separate instances of that
> database from one computer.

Generally speaking, this not an issue.  You can consolidate all three
machines to one cluster, or to multiple clusters running on same box.
The approaches have pros and cons relative to each other.  I would
personally tend to simple create different databases on the same
cluster unless you had a reason not to.  The database would share:

*) database roles (in particular superuser accounts)
*) WAL (possibly presenting a i/o bottleneck)
*) replication for HS/SR (which is all or nothing at the cluster level)
*) shared memory
*) availability -- if one database crashes they will all crash
*) tcp/ip port (this is good -- you don't have to worry about what
port you're connecting to)

merlin