Re: pg_dump and not MVCC-safe commands

[Guillaume Lelarge]

Hi,

Le lun. 20 mai 2024 à 11:27, PetSerAl  a écrit :

> How pg_dump interact with not MVCC-safe commands?
>
> As I understand, pg_dump first take snapshot and then lock all tables
> it intended to dump. What happens if not MVCC-safe command committed
> after snapshot but before lock? From comment to pg_dump.c I understand
> that it may fail with 'cache lookup failed' error. But, can it happen,
> that pg_dump not fail, but instead capture inconsistent dump? For
> example TRUNCATE committed after snapshot and pg_dump will see result
> of TRUNCATE but not result of other commands in TRUNCATE transaction?
>
>
>
You can't truncate an already existing table while pg_dump is running.
TRUNCATE needs an exclusive lock, and pg_dump already has a lock on all
tables of the database it's dumping. So TRUNCATE will be blocked until
pg_dump finishes all its work.

(The same will happen for VACUUM FULL, CLUSTER and some (all?) ALTER TABLE
commands.)


-- 
Guillaume.

Re: xmax not zero?

[Guillaume Lelarge]

Hi,

Le lun. 30 oct. 2023 à 13:45, Luca Ferrari  a écrit :

> Hi all,
> I have a table that presents an xmax not zeroed outside of a
> transaction block, and it does not look normal to me.
> I have no idea about how this happened, it is one of my "toy"
> PostgreSQL virtual machines.
> But how to dig an better understand why there is an xmax with a non-zero
> value?
>

There are many reasons for a non-zero value: row updated or deleted in a
rollbacked transaction, row updated or deleted in a current transaction,
row locked by a SELECT FOR UPDATE, and perhaps others I don't remember
right now.


> I've a snapshot for doing more tests.
>
> testdb=> select version();
> version
>
> --
> PostgreSQL 16.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.3.1
> 20221121 (Red Hat 11.3.1-4), 64-bit
> (1 row)
>
> testdb=> select txid_current() as me, xmin, xmax, pk /* table real
> column */, pg_snapshot_xmin( pg_current_snapshot() ), pg_snapshot_
> xmax( pg_current_snapshot() ), pg_current_snapshot() from automobili limit
> 5;
>   me|  xmin   |  xmax   |   pk| pg_snapshot_xmin |
> pg_snapshot_xmax | pg_current_snapshot
>
> -+-+-+-+--+--+-
> 1713451 | 1533610 | 1675700 | 501 |  1713451 |
> 1713451 | 1713451:1713451:
> 1713451 | 1533610 | 1675700 | 503 |  1713451 |
> 1713451 | 1713451:1713451:
> 1713451 | 1533610 | 1675700 | 505 |  1713451 |
> 1713451 | 1713451:1713451:
> 1713451 | 1533610 | 1675700 | 507 |  1713451 |
> 1713451 | 1713451:1713451:
> 1713451 | 1533610 | 1675700 | 509 |  1713451 |
> 1713451 | 1713451:1713451:
>
>
> testdb=> vacuum verbose automobili;
> INFO:  vacuuming "testdb.luca.automobili"
> INFO:  launched 1 parallel vacuum worker for index cleanup (planned: 1)
> INFO:  finished vacuuming "testdb.luca.automobili": index scans: 0
> pages: 0 removed, 12738 remain, 12738 scanned (100.00% of total)
> tuples: 0 removed, 100 remain, 0 are dead but not yet removable
> removable cutoff: 1713454, which was 0 XIDs old when operation ended
> new relfrozenxid: 1713454, which is 179844 XIDs ahead of previous value
> frozen: 12738 pages from table (100.00% of total) had 100 tuples frozen
> index scan not needed: 0 pages from table (0.00% of total) had 0 dead
> item identifiers removed
> I/O timings: read: 273.835 ms, write: 108.286 ms
> avg read rate: 25.819 MB/s, avg write rate: 25.880 MB/s
> buffer usage: 12776 hits, 12711 misses, 12741 dirtied
> WAL usage: 38215 records, 12741 full page images, 60502693 bytes
> system usage: CPU: user: 0.56 s, system: 0.21 s, elapsed: 3.84 s
> INFO:  vacuuming "testdb.pg_toast.pg_toast_76512"
> INFO:  finished vacuuming "testdb.pg_toast.pg_toast_76512": index scans: 0
> pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
> tuples: 0 removed, 0 remain, 0 are dead but not yet removable
> removable cutoff: 1713454, which was 0 XIDs old when operation ended
> new relfrozenxid: 1713454, which is 186042 XIDs ahead of previous value
> frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
> index scan not needed: 0 pages from table (100.00% of total) had 0
> dead item identifiers removed
> I/O timings: read: 0.520 ms, write: 0.000 ms
> avg read rate: 9.902 MB/s, avg write rate: 0.000 MB/s
> buffer usage: 19 hits, 1 misses, 0 dirtied
> WAL usage: 1 records, 0 full page images, 188 bytes
> system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
> VACUUM
>
> testdb=> select txid_current(), xmin, xmax, pk from automobili limit 5;
> txid_current |  xmin   | xmax |   pk
> --+-+--+-
>  1713454 | 1533610 |0 | 501
>  1713454 | 1533610 |0 | 503
>  1713454 | 1533610 |0 | 505
>  1713454 | 1533610 |0 | 507
>  1713454 | 1533610 |0 | 509
> (5 rows)
>
>
>
Regards.


-- 
Guillaume.

Re: setting up streaming replication, part 2

[Guillaume Lelarge]

Hi,

Le mer. 25 oct. 2023 à 02:29, Brad White  a écrit :

> I have the replication server set up and the streaming is working.
> The latest data show up, as desired.
>
> 3 minor issues.
>
> 1) I also have the WAL files being copied to a common location from the
> primary server with
>   archive_command = 'copy %p
> "DISKSTATION\\AccessData\\Prod\\WALfiles\\%f"'
>
> It's not clear to me how the replication server finds those files.
>
>
The secondary finds those files if you set up the restore_command GUC on it.


> I also have the cleanup set to go, but it doesn't seem to be cleaning up
> any of the archived files.
> archive_cleanup_command = 'pg_archivecleanup
> DISKSTATION\\AccessData\\Prod\\WALfiles %r'
>
>
This needs to be set up on the secondary. The primary won't care about that
setup.


> 2) I have the connection string set for the replication server to connect,
>  primary_conninfo = 'host = 192.168.1.112 port = 5433 user =
> {pg_rep_user }password = {password}'
> But the log file shows
> LOG:  waiting for WAL to become available at 2/A1B8
> FATAL:  could not connect to the primary server: connection to server
> at "{IP}", port 5433 failed: fe_sendauth: no password supplied
>
>

You need to set up the .pgpass file (on Unix) or pgpass.conf (on Windows).
Seems quite complex to figure out where to put that file on Windows.

3) When I run
> "C:\Program Files\PostgreSQL\15\bin\pg_ctl.exe" promote -D "C:\Program
> Files\PostgreSQL\15\data"
> on the backup to promote it to Primary, I get
> pg_ctl: could not send promote signal (PID: 3996): Operation not
> permitted
>
>
I guess you need to be the postgres user to execute that. On Linux, I would
use sudo. Don't know for WIndows.


-- 
Guillaume.

Re: what is the solution like oracle DB's datafile

[Guillaume Lelarge]

Le mar. 27 déc. 2022 à 11:37, Eagna  a écrit :

>
>
>  wrote:
>
> > - Oracle was developed earlier, and one can argue that in those days
> file systems
> > were not so great, so there was more need to write your own
>
>
> Correct me if I'm wrong, but didn't Postgres (or Ingres upon which it's
> based) development start in the early-to-mid 70's and Oracle's only in the
> late 70's - early 80's?
>
> [1] https://en.wikipedia.org/wiki/Ingres_(database)#Early_history
> [2] https://en.wikipedia.org/wiki/PostgreSQL#History
>
>
" The origins of PostgreSQL date back to 1986 as part of the POSTGRES
project at the University of California at Berkeley and has more than 35
years of active development on the core platform." (
https://www.postgresql.org/about/)


-- 
Guillaume.

Re: best practice to patch a postgresql version?

[Guillaume Lelarge]

Le mar. 27 déc. 2022 à 06:33, qihua wu  a écrit :

> Thanks Ron,
>
> But on a critical production database, we need to cut down the downtime as
> much as possible. If just remove a version, and then install a new version,
> both of them need a downtime. If we can install several versions on
> different location, switching version will have a shorter downtime: just
> stop the old version and start using the new binary, and we have no
> downtime when remove/install a new version.
>
>
If you really want to have different minor releases installed on one
computer, you'll have to compile them, and specify an install directory at
the configure step. See https://www.postgresql.org/docs/15/installation.html
for more information.

On Mon, Dec 26, 2022 at 11:54 PM Ron  wrote:
>
>> Just downgrade the packages if you need to revert to a previous version.
>>
>> Remove the 14*.5* package, and install the 14*.4* package (because no
>> one's crazy enough to start with 14.0 in December 2022).  You'll have to
>> explicitly specify the version number.
>>
>> On 12/26/22 03:29, qihua wu wrote:
>>
>> We are planning to use postgresq on production, but there is one question
>> about how to patch a db. We don't want to overwrite the old version
>> directly, so that we can rollback if the new version has issues.  So we
>> want to install it a different location such as /home/postgres/14.1 for
>> version 14.1 (all binary should be under 14.1 or sub-fold of 14.1) and
>> /home/postgres/14.0 for 14.0, in this way we can easily switch between
>> different versions. But apt install on ubuntu doesn't have the option for a
>> customized location. So what's the best practice to patch postgres?
>>
>>
>> --
>> Born in Arizona, moved to Babylonia.
>>
>

-- 
Guillaume.

Re: pg_restore remap schema

[Guillaume Lelarge]

Hi,

Le mer. 16 nov. 2022 à 13:08, Fabrice Chapuis  a
écrit :

> Hi,
> I worked on the pg_dump source code to add remap schema functionality to
> use it internally where I work. This is a first version that allows to
> remap tables, views and sequences (only to export schema). Is this
> development likely to interest the PG community and to continue this
> development further?
>
>
If it doesn't work on functions, that will be a big issue. And to be
honest, I don't think you can do it reliably on functions, especially with
dynamic queries in PL/pgsql.

Though it would still be interesting to see your patch.


-- 
Guillaume.

Re: "set role" semantics

[Guillaume Lelarge]

Hi,

Le mer. 9 nov. 2022, 19:55, Bryn Llewellyn  a écrit :

> adrian.kla...@aklaver.com wrote:
>
> david.g.johns...@gmail.com wrote:
>
> b...@yugabyte.com wrote:
>
> Notice that I didn't grant "connect" on either of the databases, "d1" or
> "d2", to any of the roles, "clstr$mgr, "d1$mgr", or "d2$mgr".
>
>
> You didn't have to since PUBLIC gets that privilege and you didn't revoke
> it.
>
> https://www.postgresql.org/docs/current/ddl-priv.html
>
>
> Revoking PUBLIC has been explained before to you (Bryn Llewellyn).
>
> A quick search:
>
> https://www.postgresql.org/message-id/2176817.1644613...@sss.pgh.pa.us
>
>
> https://www.postgresql.org/message-id/cakfquwayij%3daqrqxjhfuj3qejq3e-pfibjj9cohx_l_46be...@mail.gmail.com
>
>
> https://www.postgresql.org/message-id/cakfquwzvq-lergmtn0e3_7mqhjwtujuzf0gsnkg32mh_qf2...@mail.gmail.com
>
>
> Here's an extract from the script that I copied in my first email:
>
>
>
>
>
>
> *create database d1;revoke all on database d1 from public;create database
> d2;revoke all on database d2 from public;*
>
> Didn't I do exactly what you both said that I failed to do?
>

Nope. All you did was revoking all privileges on these database objects. It
didn't revoke privileges on objects of these databases. In other words, you
revoked CREATE, TEMP, VONNECT privileges on d1 and d2, you didn't revoke
privileges on the public schema.


> **Summary**
>
> My experiments (especially below) show that "set role" has special
> semantics that differ from starting a session from cold:
>
> *"set role" allows a role that lacks "connect" on some database to end up
> so that the "current_database()" shows that forbidden database.*
>
> My question still stands: where can I read the account of this? I'm also
> interested to know _why_ it was decided not to test for the "connect"
> privilege when "set role" is used.
>

Using SET ROLE doesn't connect you as another role on the database. You can
see this by logging connections, you won't see any connection log lines
when using SET ROLE. It also doesn't check pg_hba.conf rules when using SET
ROLE.

SET ROLE only makes you impersonate another role. The only privilege you
need to do that is being a member of this role.


> **Detail**
>
> I suppose that the script that I first showed you conflated too many
> separable notions. (My aim was to you show what my overall aim was). Here's
> a drastically cut down version. It still demonstrates the behavior that I
> asked about.
>
> *create role joe*
> *  nosuperuser*
> *  nocreaterole*
> *  nocreatedb*
> *  noreplication*
> *  nobypassrls*
> *  connection limit -1*
> *  login password 'p';*
>
> *create database d1;*
> *revoke all on database d1 from public;*
>
> *\c d1 postgres*
>
> *set role joe;*
> *select current_database()||' > '||session_user||' > '||current_user*
> *;*
> I'm still able to end up with "Joe" as the "current_user" and "d1" (to
> which Joe cannot connect) as the "current_database()".
>

Because SET ROLE doesn't connect you as this role name.


> I then did the sanity test that I should have shown you at the outset.
> (Sorry that I didn't do that.) I started a session from cold, running
> "psql" on a client machine where the server machine is called "u" (for
> Ubuntu) in my "/etc/hosts", thus:
>
>
> *psql -h u -p 5432 -d d1 -U joe*
> The connect attempt was rejected with the error that I expected: "User
> does not have CONNECT privilege".
>

Because joe tried to connect on d1, and he doesn't have the privileges to
do so.


> I wondered if the fact that the "session_user" was "postgres" in my tests
> was significant. So I did a new test. (As ever, I started with a freshly
> created cluster to be sure that no earlier tests had left a trace.)
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *create role
> mary  nosuperuser  noinherit  nocreaterole  nocreatedb  noreplication  
> nobypassrls  connection
> limit -1  login password 'p';create role
> joe  nosuperuser  noinherit  nocreaterole  nocreatedb  noreplication  
> nobypassrls  connection
> limit -1  login password 'p';create database d1;revoke all on database d1
> from public;grant connect on database d1 to mary;grant joe to mary;*
> Then I did this on the client machine:
>
> *psql -h u -p 5432 -d d1 -U mary*
>
> *set role joe;*
> Here, too, I ended up with "Joe" as the "current_user" and "d1" (to which
> Joe cannot connect) as the "current_database()".
>
>

Re: Unable to use pg_verify_checksums

[Guillaume Lelarge]

Hi,

Le ven. 4 nov. 2022 à 07:12, shashidhar Reddy 
a écrit :

> Hello,
>
> I am using postgres version 11, in the process of upgrade using pg_upgrade
> I am trying to run checksums on version 11 but getting error
>
> pg_verify_checksums: data checksums are not enabled in cluster
>
> when trying to enable /pg_verify_checksums: unrecognized option '--enable'
>
>
Yeah, that's why it's called pg_verify_checksums: it only does a check of
checksums, it can't enable checksums.

Look at https://www.postgresql.org/docs/11/pgverifychecksums.html, there's
no --enable option.

I need help on this.
>
>
Well, on v11, you need to initdb to enable checksums. So dump your
databases, initdb with checksums, and then restore your databases.

Regards.


-- 
Guillaume.

Re: Number of updated rows with LibPQ

[Guillaume Lelarge]

Le ven. 14 oct. 2022 à 13:52, Dominique Devienne  a
écrit :

> On Wed, Oct 5, 2022 at 8:17 PM Tom Lane  wrote:
> > Laurenz Albe  writes:
> > > On Wed, 2022-10-05 at 16:38 +0200, Dominique Devienne wrote:
> > > Yes, you have to use PQcmdTuples(), and you have to convert the string
> to an integer.
> > > But don't worry: the result will *not* be "INSERT 0 5", it will be
> just "5"
>
> Thanks. What's the leading 0 though, then?
>

The leading number was the OID of the inserted row if you only had one row
and if the table had OID on rows. Otherwise, it was zero. It's always 0
nowadays since you can't have OID on rows.


-- 
Guillaume.

Re: recovery.conf and archive files

[Guillaume Lelarge]

Le jeu. 13 oct. 2022 à 12:42, Rita  a écrit :

> The primary's recovery.conf looks like this
>
> listen_address='*'
> wal_level=replica
> synchronous_commit=local
> archive_move = on
> archive_command = 'cp %p /var/lib/pgsql/11/data/archive/%f'
> max_wal_senders = 10
> wal_keep_segments=10
> synchronous_standby_names='standby0'
> wal_log_hints=on
>
>
The archive command stores the WAL in a local directory. That's what I said
earlier.


>
> On Sun, Oct 9, 2022 at 8:45 AM Guillaume Lelarge 
> wrote:
>
>> Hi,
>>
>> Le dim. 9 oct. 2022 à 13:54, Rita  a écrit :
>>
>>> I have primary and standby replication setup.
>>>
>>> On my primary the archive directory is rather large (30GB) and growing.
>>> On my standby I have recovery.conf which has
>>> archive_cleanup_command = 'pg_archivecleanup -d
>>> /var/lib/pgsql/11/data/archive %r'
>>>
>>> I was under the impression this line would remove data from my primary
>>> AND standby. Is that not the case?
>>>
>>>
>> pg_archivecleanup will clean up the *local* directory. It won't clean up
>> the archive directory if it's stored on the primary.
>>
>> If I misunderstood your issue, it would be great to send us the
>> postgresql.conf file from your primary.
>>
>>
>> --
>> Guillaume.
>>
>
>
> --
> --- Get your facts first, then you can distort them as you please.--
>


-- 
Guillaume.

Re: Weird planner issue on a standby

[Guillaume Lelarge]

Le mer. 12 oct. 2022 à 08:56, Guillaume Lelarge  a
écrit :

> Le mar. 11 oct. 2022 à 19:42, Guillaume Lelarge 
> a écrit :
>
>> Le mar. 11 oct. 2022 à 18:27, Alvaro Herrera  a
>> écrit :
>>
>>> On 2022-Oct-11, Tom Lane wrote:
>>>
>>> > Are there any tables in this query where extremal values of the join
>>> > key are likely to be in recently-added or recently-dead rows?  Does
>>> > VACUUM'ing on the primary help?
>>>
>>> I remember having an hypothesis, upon getting a report of this exact
>>> problem on a customer system once, that it could be due to killtuple not
>>> propagating to standbys except by FPIs.  I do not remember if we proved
>>> that true or not.  I do not remember observing that tables were being
>>> read, however.
>>>
>>>
>> Thanks for your answers.
>>
>> The last predicate is "and c3>='2020-10-10' and c3<='2022-10-10'. I have
>> no idea on the actual use of c3 but rows with c3 at '2022-10-10' (which is
>> yesterday) is much probably recently-added. I can ask my customer if you
>> want but this looks like a pretty safe bet.
>>
>> On the VACUUM question, I didn't say, but we're kind of wondering if it
>> was lacking a recent-enough VACUUM. So they're doing a VACUUM tonight on
>> the database (and especially on the 1.6TB table which is part of the
>> query). I'm kind of skeptical because if the VACUUM wasn't enough on the
>> standby, it should be the same on the primary.
>>
>>
> It appears that I was wrong. I just got an email from my customer saying
> they got their performance back after a VACUUM on the two main tables of
> the query. I'll have them on the phone in about an hour. I'll probably know
> more then. Still wondering why it was an issue on the standby and not on
> the primary. VACUUM cleans up tables and indexes, and this activity goes
> through WAL, doesn't it?
>
>
Just finished my phone call. So, they definitely have their performance
back. All they did was a VACUUM on two tables.

If I understand correctly, during "normal" operations, some information is
stored on the primary and sent to standbys. For some reason, only the
primary take them into account, standbys ignore them. That would explain
why, when we promoted a standby without doing anything else, it had much
better performance. VACUUM fixes the issue on a standby, probably by
storing this information in a different way. After VACUUM, standbys stop
ignoring this information, which helps get the performance back.

That sounds like a plausible explanation. I still have questions if you
don't mind:
* what is this information?
* where is it stored? my guess would be indexes
* why is it ignored on standbys and used on primary?

We didn't talk much about releases, so I guess that the
"standby-ignores-some-information" part is currently on all available
releases?

Thank you.


-- 
Guillaume.

Re: Weird planner issue on a standby

[Guillaume Lelarge]

Le mar. 11 oct. 2022 à 19:42, Guillaume Lelarge  a
écrit :

> Le mar. 11 oct. 2022 à 18:27, Alvaro Herrera  a
> écrit :
>
>> On 2022-Oct-11, Tom Lane wrote:
>>
>> > Are there any tables in this query where extremal values of the join
>> > key are likely to be in recently-added or recently-dead rows?  Does
>> > VACUUM'ing on the primary help?
>>
>> I remember having an hypothesis, upon getting a report of this exact
>> problem on a customer system once, that it could be due to killtuple not
>> propagating to standbys except by FPIs.  I do not remember if we proved
>> that true or not.  I do not remember observing that tables were being
>> read, however.
>>
>>
> Thanks for your answers.
>
> The last predicate is "and c3>='2020-10-10' and c3<='2022-10-10'. I have
> no idea on the actual use of c3 but rows with c3 at '2022-10-10' (which is
> yesterday) is much probably recently-added. I can ask my customer if you
> want but this looks like a pretty safe bet.
>
> On the VACUUM question, I didn't say, but we're kind of wondering if it
> was lacking a recent-enough VACUUM. So they're doing a VACUUM tonight on
> the database (and especially on the 1.6TB table which is part of the
> query). I'm kind of skeptical because if the VACUUM wasn't enough on the
> standby, it should be the same on the primary.
>
>
It appears that I was wrong. I just got an email from my customer saying
they got their performance back after a VACUUM on the two main tables of
the query. I'll have them on the phone in about an hour. I'll probably know
more then. Still wondering why it was an issue on the standby and not on
the primary. VACUUM cleans up tables and indexes, and this activity goes
through WAL, doesn't it?


-- 
Guillaume.

Re: Weird planner issue on a standby

[Guillaume Lelarge]

Le mer. 12 oct. 2022 à 06:08, Ron  a écrit :

> On 10/11/22 22:35, Julien Rouhaud wrote:
> > On Tue, Oct 11, 2022 at 07:42:55PM +0200, Guillaume Lelarge wrote:
> >> Le mar. 11 oct. 2022 à 18:27, Alvaro Herrera 
> a
> >> écrit :
> >>
> >>> On 2022-Oct-11, Tom Lane wrote:
> >>>
> >>>> Are there any tables in this query where extremal values of the join
> >>>> key are likely to be in recently-added or recently-dead rows?  Does
> >>>> VACUUM'ing on the primary help?
> >>> I remember having an hypothesis, upon getting a report of this exact
> >>> problem on a customer system once, that it could be due to killtuple
> not
> >>> propagating to standbys except by FPIs.  I do not remember if we proved
> >>> that true or not.  I do not remember observing that tables were being
> >>> read, however.
> >>>
> >>>
> >> Thanks for your answers.
> >>
> >> The last predicate is "and c3>='2020-10-10' and c3<='2022-10-10'. I
> have no
> >> idea on the actual use of c3 but rows with c3 at '2022-10-10' (which is
> >> yesterday) is much probably recently-added. I can ask my customer if you
> >> want but this looks like a pretty safe bet.
> >>
> >> On the VACUUM question, I didn't say, but we're kind of wondering if it
> was
> >> lacking a recent-enough VACUUM. So they're doing a VACUUM tonight on the
> >> database (and especially on the 1.6TB table which is part of the query).
> >> I'm kind of skeptical because if the VACUUM wasn't enough on the
> standby,
> >> it should be the same on the primary.
> >>
> >> Actually, there are two things that really bug me:
> >> * why the difference between primary and both standbys?
> >> * why now? (it worked great before this weekend, and the only thing I
> know
> >> happened before is a batch delete on sunday... which may be a
> good-enough
> >> reason for things to get screwed, but once again, why only both
> standbys?)
> >>
> >> Julien Rouhaud also told me about killtuples, but I have no idea what
> they
> >> are. I suppose this is different from dead tuples. Anyway, if you can
> >> enlighten me, I'll be happy :)
> > That's an optimisation where an index scan can mark an index entry as
> dead
> > (LP_DEAD) if if tries to fetch some data from the heap that turns out to
> be all
> > dead, so further scans won't have to check again (you can grep
> kill_prior_tuple
> > in the source for more details).  As that's a hint bit, it may not be
> > replicated unless you enable wal_log_hints or data_checksums (or write
> it as a
> > FPI indeed), which could explain discrepancy between primary (after a
> first
> > slow index scan) and standby nodes.
> >
> > But since your customer recreated their standbys from scratch *after*
> that
> > delete, all the nodes should have those hint bits set (Guillaume
> confirmed
> > off-list that they used a fresh BASE_BACKUP).  Note that Guillaume also
> > confirmed off-list that the customer has checksums enabled, which means
> that
> > MarkBufferDirtyHint() should be guaranteed to mark the buffers as dirty,
> so I'm
> > out of ideas to explain the different behavior on standbys.
>
> Would EXPLAIN (VERBOSE, COSTS, FORMAT JSON) run on both nodes help show
> any
> differences?
>
>
No differences.


-- 
Guillaume.

Re: Weird planner issue on a standby

[Guillaume Lelarge]

Le mar. 11 oct. 2022 à 18:27, Alvaro Herrera  a
écrit :

> On 2022-Oct-11, Tom Lane wrote:
>
> > Are there any tables in this query where extremal values of the join
> > key are likely to be in recently-added or recently-dead rows?  Does
> > VACUUM'ing on the primary help?
>
> I remember having an hypothesis, upon getting a report of this exact
> problem on a customer system once, that it could be due to killtuple not
> propagating to standbys except by FPIs.  I do not remember if we proved
> that true or not.  I do not remember observing that tables were being
> read, however.
>
>
Thanks for your answers.

The last predicate is "and c3>='2020-10-10' and c3<='2022-10-10'. I have no
idea on the actual use of c3 but rows with c3 at '2022-10-10' (which is
yesterday) is much probably recently-added. I can ask my customer if you
want but this looks like a pretty safe bet.

On the VACUUM question, I didn't say, but we're kind of wondering if it was
lacking a recent-enough VACUUM. So they're doing a VACUUM tonight on the
database (and especially on the 1.6TB table which is part of the query).
I'm kind of skeptical because if the VACUUM wasn't enough on the standby,
it should be the same on the primary.

Actually, there are two things that really bug me:
* why the difference between primary and both standbys?
* why now? (it worked great before this weekend, and the only thing I know
happened before is a batch delete on sunday... which may be a good-enough
reason for things to get screwed, but once again, why only both standbys?)

Julien Rouhaud also told me about killtuples, but I have no idea what they
are. I suppose this is different from dead tuples. Anyway, if you can
enlighten me, I'll be happy :)


-- 
Guillaume.

Weird planner issue on a standby

[Guillaume Lelarge]

Hello,

My customer has a really weird planner issue on a standby.

First, the context. There's a primary, and two standbys, all on a 11.8
release. (I know this isn't the latest release for the v11 branch.) The
cluster is 5.5TB. The PostgreSQL configuration is the same on all servers.
pg_db_role_setting is almost empty, and there's nothing specific to
planning and replication.

Here is the issue. Queries on both standbys take a lot more time than on
the primary. We eventually found that the queries take a lot of time to be
planned, not to be executed. For example:

On the primary:
 Planning Time: 1806.574 ms
 Execution Time: 771.888 ms
On any of the standbys:
 Planning Time: 41566.439 ms
 Execution Time: 1125.352 ms

A simple EXPLAIN already shows this difference in duration.

The query in itself isn't really that big. 8 tables (without partitions), a
few predicates. Nothing fancy. Nothing huge to plan. Here it is, a bit
anonymized:

select ...
from t1
left outer join t2 on ...
left outer join t3 on ...
left outer join t4 on ...
left outer join t5 on ...
left outer join t6 on ...
left outer join t7 on ...
left outer join t8 on ...
where c1='...' and c2='...'
  and c3>='...' and c4<='...' ;

Join conditions are really simple. There's no function called anywhere.

Plans on the three servers are exactly the same. Same nodes, same
statistics, same calculations' results.

Moreover, we've looked into what the planner was doing with strace, and
we've been surprised that it involved massive reading on tables (more than
130,000 calls to read() on 15 files). We found that most of these files are
the files for one of the tables on this query. Also, this massive reading
happens only on standbys, not on the primary.

Our customer took the time to drop and create the standbys yesterday night,
but the new standbys still have the issue.

And finally, we discovered that promoting a standby brings back the old
performance. We have the same performance between the primary and the
promoted standby.

To be honest, I'm lost, I don't know what to do next. But I have some
questions :)

* The planner seems to read tables to generate the plans and/or select the
right plan. Why does it do that? I thought it only reads indexes if needed,
but it is quite clear it reads tables also.
* How can the replication have an impact on the planner ? (I'm not sure I'm
asking the right question... I mean, why do I see a difference in behaviour
between a standby and an old standby, new autonomous server?)
* Do you have any idea on how to fix this?

Thank you.

Regards.


-- 
Guillaume.

Re: recovery.conf and archive files

[Guillaume Lelarge]

Hi,

Le dim. 9 oct. 2022 à 13:54, Rita  a écrit :

> I have primary and standby replication setup.
>
> On my primary the archive directory is rather large (30GB) and growing. On
> my standby I have recovery.conf which has
> archive_cleanup_command = 'pg_archivecleanup -d
> /var/lib/pgsql/11/data/archive %r'
>
> I was under the impression this line would remove data from my primary AND
> standby. Is that not the case?
>
>
pg_archivecleanup will clean up the *local* directory. It won't clean up
the archive directory if it's stored on the primary.

If I misunderstood your issue, it would be great to send us the
postgresql.conf file from your primary.


-- 
Guillaume.

Re: Re[2]: CVE-2022-2625

[Guillaume Lelarge]

Le jeu. 15 sept. 2022 à 16:52, misha1966 misha1966  a
écrit :

> Is there a patch for 9.6 ?
>

A quick Google search for "postgres CVE-2022-2625" gives you
https://www.postgresql.org/support/security/CVE-2022-2625/. And this page
tells you there's only a fix for releases 10 to 14. Moreover, fixes in 2022
won't have a patch for releases prior to v10.


>
>
> Четверг, 15 сентября 2022, 17:55 +09:00 от Ron :
>
> Software is only certified for 9.5?  Hopefully you're running 9.5.25.
>
> I feel your pain... we've got some databases that will stay at 9.6 for
> another year.
>
> On 9/14/22 23:24, misha1966 misha1966 wrote:
>
> All business processes are hooked on postgresql 9.5. There is no way to
> update.
> Unfortunately, I don't have the proper qualifications to change it.
>
>
> Четверг, 15 сентября 2022, 1:58 +09:00 от Laurenz Albe
> 
> :
>
> On Wed, 2022-09-14 at 17:02 +0300, misha1966 misha1966 wrote:
> > Tell me, is there a CVE-2022-2625 vulnerability in posgresql 9.5?
> > If so, who knows how to patch it? Patches from version 10 are not
> suitable at all...
>
> Yes, that vulnerability exists in 9.5.
>
> To patch that, you'd have to try and backpatch the commit to 9.5 yourself:
>
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=b9b21acc766db54d8c337d508d0fe2f5bf2daab0
>
> Since 9.5 is out of support, there are no more bugfixes for it provided
> by the community. If security were a real concern for you, you would
> certainly not be running a PostgreSQL version that is out of support.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
>
>
>
> --
> Angular momentum makes the world go 'round.
>
>
>


-- 
Guillaume.

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

[Guillaume Lelarge]

Le mer. 14 sept. 2022 à 00:35, Bryn Llewellyn  a écrit :

>
> *guilla...@lelarge.info  wrote:*
> This won't answer your question
>
>
> It has been answered now. See my "case closed" email here:
>
>
> www.postgresql.org/message-id/B33C40D9-2B79-44C7-B527-86E672BEA71A%40yugabyte.com
>
> …but still… I usually really like your scripts, it's nicely written, but
> this part seems really weird to me:
>
> *b...@yugabyte.com  wrote:*
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *-- No errordo $body$declare  p int not null := 0;begin  for p in (
> select pidfrom pg_stat_activitywhere backend_type =  'client
> backend'and   pid  <> pg_backend_pid())  loopperform
> pg_terminate_backend(p);  end loop;end;$body$;*
>
>
> While your script works great, I'm wondering why you don't write it this
> way:
>
> SELECT pg_terminate_backend(pid) FROM pg_stat_activity
> WHERE backend_type =  'client backend' AND pid <> pg_backend_pid();
>
> As it is less code, it's quicker to understand what it does.
>
>
> Well, yes… I have often been accused of being anally fixated on
> details—and of verbosity. This is just the old chestnut that a "select"
> statement shouldn't have side effects.
>

Oh, OK, looks like a good reason to me. I will probably still do the quick
SELECT, but I understand your view on it.


> "pg_terminate_backend(p)" ought, by the book, to be a procedure. But I
> suppose that it dates from the time when PG had only user-defined functions
> (and no shipped procedures). And "perform" makes a function feel to me to
> be a bit more like a procedure than just selecting it feels. Others might
> well disagree…
>


-- 
Guillaume.

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

[Guillaume Lelarge]

Hi,

This won't answer your question but still... I usually really like your
scripts, it's nicely written, but this part seems really weird to me:

Le mar. 13 sept. 2022 à 20:23, Bryn Llewellyn  a écrit :

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *-- No errordo $body$declare  p int not null := 0;begin  for p in (
>   select pidfrom pg_stat_activitywhere backend_type =  'client
> backend'and   pid  <> pg_backend_pid())  loopperform
> pg_terminate_backend(p);  end loop;end;$body$;*
>

While your script works great, I'm wondering why you don't write it this
way:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE backend_type =  'client backend' AND pid <> pg_backend_pid();

As it is less code, it's quicker to understand what it does.


-- 
Guillaume.

Re: pg_restore remap schema

[Guillaume Lelarge]

Hi,

Le lun. 8 août 2022 à 18:28, Fabrice Chapuis  a
écrit :

> Hello,
> I can't find an option with pg_restore to rename an exported schema
> schema1 -> schema2
>

That's because it doesn't exist :)


> Is a development in progress to add this option
>
>
Nope, never heard of someone working on this.

Regards.


-- 
Guillaume.

Re: "set autocommit on" in golang client query

[Guillaume Lelarge]

Hi,

Le lun. 1 août 2022 à 15:10, Rory Campbell-Lange 
a écrit :

> Apologies for the rather naive question.
>
> I have a psql migration script to call a looping procedure which commits
> batches of data along the following lines:
>
> set search_path = a, b c;
> \set AUTOCOMMIT on
> -- call procedure with nested transactions
> CALL c.pr_my_procedure(debug=>true);
>
> I'm trying to migrate this to a programme using the golang pgx module to
> exec
> this code on a large number of databases in parallel. There doesn't seem an
> obvious way of setting autocommit outside of psql, so the equivalent of
>
> conn.Exec(context.Background(),
>   "set search_path = a, b c; set AUTOCOMMIT on; CALL
> c.pr_my_procedure(debug=>true);")
>
> fails with
>
> ERROR: syntax error at or near "ON"
>
> while
>
> conn.Exec(context.Background(),
>   "set search_path = a, b c; CALL
> c.pr_my_procedure(debug=>true);")
>
> fails with
>
> ERROR: invalid transaction termination
>
> How should I set autocommit on? The "Create Procedure" docs at
> https://www.postgresql.org/docs/current/sql-createprocedure.html don't
> seem to
> mention autocommit.
>
>
That's because it doesn't exist on the PostgreSQL server. You can use it
with psql but that's a psql feature, not the server's.


-- 
Guillaume.

Re: Unable to archive logs in standby server

[Guillaume Lelarge]

Hi,

Le jeu. 21 juil. 2022 à 13:58, Meera Nair  a écrit :

> Hi team,
>
>
>
> With non-exclusive backup method, trying backup from standby node.
>
> But pg_stop_backup function returns “WAL archiving is not enabled…” and
> the logs are not archived to WAL directory configured.
>
>
>
> Please check if I am missing anything in configuring this properly,
>
> Server was restarted after setting the archiving params in postgresql.conf
>
>
>
> Below is from version 14:
>
>
>
> postgres=# select pg_start_backup('label', false, false);
>
> pg_start_backup
>
> -
>
> 0/6D8
>
> (1 row)
>
>
>
>
>
> postgres=#  select pg_stop_backup('false');
>
> NOTICE:  WAL archiving is not enabled; you must ensure that all required
> WAL segments are copied through other means to complete the backup
>
>   pg_stop_backup
>
> ---
>
> (0/60001C0,"START WAL LOCATION: 0/6D8 (file 00010006)+
>
> CHECKPOINT LOCATION: 0/6000110   +
>
> BACKUP METHOD: streamed  +
>
> BACKUP FROM: standby +
>
> START TIME: 2022-07-21 12:42:11 IST  +
>
> LABEL: label +
>
> START TIMELINE: 1+
>
> ","")
>
> (1 row)
>
>
>
>
>
> postgres=# select pg_is_in_recovery();
>
> pg_is_in_recovery
>
> ---
>
> t
>
> (1 row)
>
>
>
>
>
> postgres=# show wal_level;
>
> wal_level
>
> ---
>
> replica
>
> (1 row)
>
>
>

>
> postgres=# show archive_mode;
>
> archive_mode
>
> --
>
> on
>
> (1 row)
>
>
>


You're doing backups from the standby, and to allow archiving on the
backups, archive_mode should be set to always.


-- 
Guillaume.

Re: Proposed Translations of Updated Code of Conduct Policy

[Guillaume Lelarge]

Hi,

Le mar. 19 juil. 2022 à 08:49, Stefan Fercot 
a écrit :

> Hi,
>
> There's a typo in the french translation. "consécutis" should be "
> consécutifs".
>
> And per consistency, I'd suggest to not mix "mandat" and "termes" to speak
> about the same thing. I believe it would be better to replace "(3) termes
> consécutis" by "(3) mandats consécutifs".
>
>
Agreed on both.


> --
> Kind Regards,
> Stefan FERCOT
> EDB: https://www.enterprisedb.com
>
> --- Original Message ---
> Le samedi 16 juillet 2022 à 7:24 PM, Lucie Šimečková <
> luciesimeck...@outlook.com> a écrit :
>
> Hello all,
>
>
>
> Following the recent update of the English version of the Code of Conduct
> that clarified the term limits of CoC committee members, the PostgreSQL
> Community Code of Conduct Committee has solicited translations of the
> updated CoC.
>
>
>
> The English version of the Policy is at:
>
> https://www.postgresql.org/about/policies/coc/
>
> The following translations were contributed by:
>
>
>
>- German translation provided by Andreas Scherbaum
>- French translation provided by Guillaume Lelarge
>- Hebrew translation provided by Michael Goldberg
>- Italian translation provided by Federico Campoli and reviewed by
>Tommaso Bartoli
>- Russian translation provided by Alexander Lakhin
>- Chinese translation provided by Bo Peng
>- Japanese translation provided by Tatsuo Ishii
>
>
>
> The proposed translations are attached as plain text and PDF files to this
> message.
>
> If you have any comments or suggestions for the proposed translations,
> please bring them to our attention no later than 11:59 PM UTC on Sunday, July
> 24, 2022. What time is that in my time zone?
> https://www.timeanddate.com/worldclock/converter.html?iso=20220724T23=1440
>
> Thank you!
>
> Lucie Šimečková
>
> PostgreSQL Community Code of Conduct Committee Member
>
>
>
>
>

-- 
Guillaume.

Re: More than one Cluster on single server (single instance)

[Guillaume Lelarge]

Hi,

Le lun. 18 juil. 2022 à 09:55, Daulat  a écrit :

>
> Hello Team,
>
> We are planning to create multiple clusters on a single server (single
> instance) with PostgreSQL V.10 to run multiple applications.
>
>
Planning to create a database cluster on a release 10 sounds already like a
bad idea, as release 10 will be maintained till november. After November
2022, no more bug fixes for this release. You really should use a more
recent release.

I don't know if it is a good idea to use a single machine to run n clusters
> but we are looking to minimize the cost of servers and other resources.
>
> Please share your thoughts on this approach?
>
>
It would make things easier for you to use a single cluster, and as many
databases as you want in this cluster.


> How can we create multiple clusters under a single postgres version?
>
>
Mostly depends on your operating system, and on how you installed
PostgreSQL in the first place. The usual answer would be to run initdb for
each cluster.

How can we identify/allocate the resources to parameters like
> shared_buffers, effective_cache_size, work_mem etc in case of multiple
> clusters?
>
>
That's the hard part if you have many clusters. You will have to determine
by yourself how much memory each cluster will need. If you use only one
cluster (and many databases in it), you would use the usual formula to set
these parameters.

Regards.


-- 
Guillaume.

Re: pg_dump: VACUUM and REINDEXING

[Guillaume Lelarge]

Le sam. 7 mai 2022 à 15:27, Hasan Marzooq  a écrit :

> Hello!
>
> Thanks Guillaume and Ron!
>
> I understand REINDEXING is not required, and as Guillaume highlighted,
> vacuum will still be needed after pg_restore.
>
> Is it ok to perform a "standard" vacuum or do we need a "FULL"
> vacuum after pg_restore?
>
>
You don't need VACUUM FULL.


> Also, I think finding tables which have dead rows and then performing
> vacuum on those tables only to save some time/processing here.
>
>
Finding dead rows in a table is interesting, but not right after a
pg_restore. pg_dump only dumps live tuples, so there won't be any dead rows
right after pg_restore.


-- 
Guillaume.

Re: pg_dump: VACUUM and REINDEXING

[Guillaume Lelarge]

Le sam. 7 mai 2022 à 10:21, Ron  a écrit :

> On 5/6/22 21:35, Hasan Marzooq wrote:
>
> Hello!
>
> I've some questions around Backup & Restore.
>
> 1: Is it necessary to perform a VACUUM and REINDEXING operation after
> restoring the dump from Postgres 9.6 to Postgres 13? The dump size could be
> 1/2 TB to 1 TB.
>
>
> Perform VACUUM after there have been many updates and deletes.  There have
> been zero updates and deleted after pg_restore; therefore, *no need to
> vacuum*.
>
>
I disagree. You're right about the "zero updates and deletes", so no need
to vacuum for bloat. But you need vacuum to get the visibility map of each
relation, so that the planner can use index-only scans.


-- 
Guillaume.

Re: pg_dump: VACUUM and REINDEXING

[Guillaume Lelarge]

Hi,

Le sam. 7 mai 2022 à 04:36, Hasan Marzooq  a écrit :

> Hello!
>
> I've some questions around Backup & Restore.
>
> 1: Is it necessary to perform a VACUUM and REINDEXING operation after
> restoring the dump from Postgres 9.6 to Postgres 13? The dump size could be
> 1/2 TB to 1 TB.
>
>
You can perform a VACUUM and an ANALYZE right after restoring, but you
definitely shouldn't REINDEX.


> 2: Also, are there any other operations that are recommended to perform
> after pg_restore?
>
>
I don't think you need anything else.


> 3: What is the minimum required disk space if taking a dump on the same
> machine where the source database exists? Is it the "size of the current
> data folder x 2"?
>
>
There's definitely no rules like that. It's impossible to know before doing
it.


-- 
Guillaume.

Re: PostgreSQL : bug (ou not) in CTE Common Table Expressions or Subqueries in the FROM Clause

[Guillaume Lelarge]

Hi,

Le mar. 22 mars 2022 à 10:46, PALAYRET Jacques 
a écrit :

> Hello,
>
> # Let's consider a table defined as follows :
> CREATE TABLE weather_stations(
> id integer,
> name varchar(30),
> elev integer
> ) ;
>
> # After loading, the content :
> id| name | elev
> --+--+--
>  31069001 | TOULOUSE-BLAGNAC |  151
>  31006001 | ALBIAC AGGLOMERATION |  289
>  31010001 | LUCHON-ANTIGNAC  |  599
>  50003001 | AGON-COUTAINVILLE|2
>  50195001 | GATHEMO  |  330
> (5 lignes)
>
> ### With CTE :
> # I'm suprised by the following result, the behavior of  PostgreSQL ; is
> that a bug ? :
> = Statement 1 : =
> WITH elev_Tlse_Blagnac AS (
> SELECT elev FROM weather_stations WHERE id=31069001
> )
> SELECT id, name, elev FROM weather_stations
> WHERE elev > (
> SELECT elev FROM elev_Tlse_Blagnac WHERE id BETWEEN 3100 and 3199
> ) ;
> id| name | elev
> --+--+--
>  31006001 | ALBIAC AGGLOMERATION |  289
>  31010001 | LUCHON-ANTIGNAC  |  599
> (2 lignes)
> # According to me, the previous result is an error, because the
> parentheses are not taken into account.
> The column id is not part of elev_Tlse_Blagnac.
>
>
Not a bug, just following the SQL standard as far as I remember.


>
> # The same result as following, which is of course OK :
> = Statement 2 : =
> WITH elev_Tlse_Blagnac AS (
> SELECT elev FROM weather_stations WHERE id=31069001
> )
> SELECT id, name, elev FROM weather_stations
> WHERE elev > (
> SELECT elev FROM elev_Tlse_Blagnac
> )
> AND id BETWEEN 3100 and 3199
> ;
> id| name | elev
> --+--+--
>  31006001 | ALBIAC AGGLOMERATION |  289
>  31010001 | LUCHON-ANTIGNAC  |  599
> (2 lignes)
>
>
> ### Same weird behavior with subquery in FROM clause :
> # NOT OK (according to me), because the parentheses are not taken into
> account :
> = Statement 3 : =
> SELECT id, name, elev FROM weather_stations
> WHERE elev > (
> SELECT elev FROM (SELECT elev FROM weather_stations WHERE id=31069001)
> elev_Tlse_Blagnac WHERE id BETWEEN 3100 and 3199
> ) ;
> id| name | elev
> --+--+--
>  31006001 | ALBIAC AGGLOMERATION |  289
>  31010001 | LUCHON-ANTIGNAC  |  599
> (2 lignes)
>
> # OK, the parentheses are taken into account because there is no confusion
> with the column id (elev_Tlse_Blagnac has a column named id) :
> = Statement 4 : =
> SELECT id, name, elev FROM weather_stations WHERE elev > (
> SELECT elev FROM (SELECT * FROM weather_stations WHERE id=31069001)
> elev_Tlse_Blagnac WHERE id BETWEEN 3100 and 3199
> ) ;
> id| name | elev
> --+--+--
>  31006001 | ALBIAC AGGLOMERATION |  289
>  31010001 | LUCHON-ANTIGNAC  |  599
>  50195001 | GATHEMO  |  330
> (3 lignes)
>
> # OK (of course) :
> = Statement 5 : =
> SELECT id, name, elev FROM weather_stations WHERE elev > (
> SELECT elev FROM (SELECT * FROM weather_stations WHERE id=31069001)
> elev_Tlse_Blagnac
> )
> AND id BETWEEN 3100 and 3199
> ;
> id| name | elev
> --+--+--
>  31006001 | ALBIAC AGGLOMERATION |  289
>  31010001 | LUCHON-ANTIGNAC  |  599
> (2 lignes)
>
>
> Is that a PostgreSQL bug or not, statement 1 or statement 3 (yes according
> to me) ?
>

Not a bug, just following the SQL standard as far as I remember.


-- 
Guillaume.
http://www.dalibo.com

Re: Robust ways for checking allowed values in a column

[Guillaume Lelarge]

Le mar. 25 janv. 2022 à 14:56, Shaozhong SHI  a
écrit :

> I tried the following:
>
>  select form from mytable  where form ~
> '^Canal$|^Drain$|^Foreshore$|^inlandRiver$|^Lake$|^lockOrFlightOfLocks$|^Marsh$|^Researvoir$|^Sea$|^tidalRiver$|^Transfer$'
>
> I used ^ and $ to ensure checking of allowed values.
>
> However, 'Backyard' was selected.
>
> Why is that?
>
>
It works for me:

# select 'Backyard' ~
'^Canal$|^Drain$|^Foreshore$|^inlandRiver$|^Lake$|^lockOrFlightOfLocks$|^Marsh$|^Researvoir$|^Sea$|^tidalRiver$|^Transfer$';
┌──┐
│ ?column? │
├──┤
│ f│
└──┘
(1 row)

So you will probably need a complete and reproducible example so that we
could test it.


-- 
Guillaume.

Re: Using a different column name in a foreign table

[Guillaume Lelarge]

Hi,

Le ven. 21 janv. 2022 à 17:24, Alanoly Andrews  a
écrit :

> Hello,
>
> I see that the syntax for the creation of a foreign table allows you to
> use a column name in the FT that is different from the one in the base
> table. Such a "create foreign table" statement executes successfully and
> creates the FT. But when I query the FT, I get an error wrt to the column
> that had been renamed. See example below:
>
> create foreign table tab1_ft (
>   id int,
> name char(10) options(column_name 'newname'))
> server xx
> options(schema_name 'public', table_name 'tab1');
>
> select * from tab1_ft;
>
> ERROR:  column "newname" does not exist
> HINT:  Perhaps you meant to reference the column "tab1.name".
> CONTEXT:  Remote SQL command: SELECT id, newname FROM public.tab1
>
> So, it seems that the when the remote SQL command is composed, the mapping
> of 'newname' to the 'name' in the base table does not take effect.
> Is there a resolution to this issue?
>
>

Your foreign table definition should have the new column name. You did it
the other way around. This is how you should have done it:

create foreign table tab1_ft (
  id int,
  newname char(10) options(column_name 'name'))
server xx
options(schema_name 'public', table_name 'tab1');

Regards.


-- 
Guillaume.

Re: csv copy error

[Guillaume Lelarge]

Hi,

Le mer. 29 déc. 2021 à 22:08, ourdiaspora  a
écrit :

> Readers,
>
> Please could anyone help with the following error produced:
>
> "
> ERROR:  invalid input syntax for integer: "1,m "
> CONTEXT:  COPY exampletable, line 1, column examplenumber: "1,m "
>
> The database commands:
>
> "
> CREATE TABLE exampletable (examplenumber smallint,
> exampletitle varchar(500)
> );
> "
>
> "
>  \copy exampletable from '/local/path/to/examplefile.csv';
> "
>
> CSV file contents:
>
> "
> 1,m
> 2,m
> 9,t
> "
>
>
You should tell the CSV command you're using a CSV file. By default, COPY
thinks it's a TSV file.


-- 
Guillaume.

Re: Why does the OID jump by 3 when creating tables?

[Guillaume Lelarge]

Hi,

Le sam. 30 oct. 2021 à 10:55, Daniel Westermann (DWE) <
daniel.westerm...@dbi-services.com> a écrit :

> Hi all,
>
> as I could not find the reason in the source code, can someone tell me why
> the OID counter jumps by 3 between two create table statements?
>
> postgres=# create table t1 ( a int );
> CREATE TABLE
> postgres=# create table t2 ( a int );
> CREATE TABLE
> postgres=# select oid,relname from pg_class where relname in ('t1','t2');
>   oid  | relname
> ---+-
>  16453 | t1
>  16456 | t2
> (2 rows)
>
> These seems not to happen with other objects, e.g. namespaces:
>
> postgres=# create schema a;
> CREATE SCHEMA
> postgres=# create schema b;
> CREATE SCHEMA
> postgres=# select oid,nspname from pg_namespace where nspname in ('a','b');
>   oid  | nspname
> ---+-
>  16459 | a
>  16460 | b
> (2 rows)
>
> ... or indexes:
>
> postgres=# select oid,relname from pg_class where relname in ('i1','i2');
>   oid  | relname
> ---+-
>  16461 | i1
>  16462 | i2
>
>
When you create a table, it also creates two data types: tablename and
_tablename. For example, for your table t1, you should have a t1 type and a
_t1 type. Both have OIDs. On my cluster, your example gives me:

# select oid,relname from pg_class where relname in ('t1','t2');
┌───┬─┐
│  oid  │ relname │
├───┼─┤
│ 24635 │ t1  │
│ 24638 │ t2  │
└───┴─┘
(2 rows)

Time: 0.507 ms
# select oid, typname from pg_type where typname like '%t1' or typname like
'%t2' and oid>24000 order by oid;
┌───┬─┐
│  oid  │ typname │
├───┼─┤
│ 24636 │ _t1 │
│ 24637 │ t1  │
│ 24639 │ _t2 │
│ 24640 │ t2  │
└───┴─┘
(4 rows)

Time: 1.203 ms

The jump between t1 OID (24635) and t2 OID (24638) is the _t1 data type OID
(24636) and the t1 data type OID (24637).


-- 
Guillaume.

Re: streaming replication different versions

[Guillaume Lelarge]

Le mer. 6 oct. 2021 à 13:46, Thomas Kellerer  a écrit :

> Marc Millas schrieb am 06.10.2021 um 13:43:
> > on release 10,  I remember reading something like: streaming replication
> is NOW upward compatible.
> > which could be understood as: its possible to have a master in rel 10
> and a slave in rel 11.
>
> No, that's not possible.
>
> For streaming replication both servers must run the same major versions.
>
>
You're right for physical replication. But it works for logical
replication, which was released in version 10.


-- 
Guillaume.

Re: psql's default database on connect (our internal ref. SRP-30861)

[Guillaume Lelarge]

Le ven. 6 août 2021 à 09:51, Matthias Apitz  a écrit :

> El día viernes, agosto 06, 2021 a las 09:34:42a. m. +0200, Guillaume
> Lelarge escribió:
>
> > > No, it does not match all my examples. Read again what it says as
> > > default on --help.
> > >
> >
> > Actually, it does. You launched "psql --help" without specifying a
> > PostgreSQL username and database name, so it used your OS username as
> both
> > of them, hence the "default: testpos" for the database.
>
> Sorry, it does not:
>
> testpos@srap53dxr1:~> psql --help -Usisis | grep DBNAME
>   psql [OPTION]... [DBNAME [USERNAME]]
>   -d, --dbname=DBNAME  database name to connect to (default: "testpos")
>

This wasn't in your first examples. I agree it's misleading. I guess this
happens because psql has no idea which database it will connect to in the
end. It isn't its call, but libpq's.

Re: psql's default database on connect (our internal ref. SRP-30861)

[Guillaume Lelarge]

Le ven. 6 août 2021 à 09:25, Matthias Apitz  a écrit :

> El día viernes, agosto 06, 2021 a las 09:07:56a. m. +0200, Guillaume
> Lelarge escribió:
>
> > Le ven. 6 août 2021 à 08:53, Matthias Apitz  a écrit :
> >
> > >
> > > Hello,
> > >
> > > testpos@srap53dxr1:~> psql --help
> > >   ...
> > >   -d, --dbname=DBNAME  database name to connect to (default:
> "testpos")
> > >
> > > testpos@srap53dxr1:~> whoami
> > > testpos
> > >
> > > testpos@srap53dxr1:~> env | grep PG
> > > PGPASSWORD=
> > > PGHOST=localhost
> > > PGPORT=5432
> > >
> > > testpos@srap53dxr1:~> LANG=C psql -Usisis
> > > psql (13.3)
> > > Type "help" for help.
> > >
> > > sisis=# \l
> > >   List of databases
> > >Name|  Owner   | Encoding |   Collate   |Ctype|   Access
> > > privileges
> > >
> > >
> ---+--+--+-+-+---
> > >  postgres  | postgres | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 |
> > >  sisis | sisis| UTF8 | de_DE.UTF-8 | de_DE.UTF-8 |
> > >  ...
> > >
> > > Why psql connects to the database 'sisis'? I was expecting that it
> > > will try to connect to a database 'testpos' as this is the UNIX login
> > > and as '-d' was not specified. I was expecting in this case an
> > > error like this:
> > >
> > > testpos@srap53dxr1:~> export PGDATABASE=testpos
> > > testpos@srap53dxr1:~> psql -Usisis
> > > psql: error: FATAL:  database »testpos« does not exist
> > >
> > > What do I uderstand wrong?
> > >
> >
> > When you try to connect without specifying a database name on the command
> > line, psql will check PGDATABASE. If unset, it will use the PostgreSQL
> user
> > name also as the database name. If you don't specify explicitly the
> > PostgreSQL username, it will check PGUSER and, if unset, will use the OS
> > username.
> >
> > That matches all your examples above.
>
> No, it does not match all my examples. Read again what it says as
> default on --help.
>

Actually, it does. You launched "psql --help" without specifying a
PostgreSQL username and database name, so it used your OS username as both
of them, hence the "default: testpos" for the database.

Re: psql's default database on connect (our internal ref. SRP-30861)

[Guillaume Lelarge]

Le ven. 6 août 2021 à 08:53, Matthias Apitz  a écrit :

>
> Hello,
>
> testpos@srap53dxr1:~> psql --help
>   ...
>   -d, --dbname=DBNAME  database name to connect to (default: "testpos")
>
> testpos@srap53dxr1:~> whoami
> testpos
>
> testpos@srap53dxr1:~> env | grep PG
> PGPASSWORD=
> PGHOST=localhost
> PGPORT=5432
>
> testpos@srap53dxr1:~> LANG=C psql -Usisis
> psql (13.3)
> Type "help" for help.
>
> sisis=# \l
>   List of databases
>Name|  Owner   | Encoding |   Collate   |Ctype|   Access
> privileges
>
> ---+--+--+-+-+---
>  postgres  | postgres | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 |
>  sisis | sisis| UTF8 | de_DE.UTF-8 | de_DE.UTF-8 |
>  ...
>
> Why psql connects to the database 'sisis'? I was expecting that it
> will try to connect to a database 'testpos' as this is the UNIX login
> and as '-d' was not specified. I was expecting in this case an
> error like this:
>
> testpos@srap53dxr1:~> export PGDATABASE=testpos
> testpos@srap53dxr1:~> psql -Usisis
> psql: error: FATAL:  database »testpos« does not exist
>
> What do I uderstand wrong?
>

When you try to connect without specifying a database name on the command
line, psql will check PGDATABASE. If unset, it will use the PostgreSQL user
name also as the database name. If you don't specify explicitly the
PostgreSQL username, it will check PGUSER and, if unset, will use the OS
username.

That matches all your examples above.


-- 
Guillaume.

Re: pg_dumpall with individual output files per database?

[Guillaume Lelarge]

Le ven. 23 juil. 2021 à 12:06, Luca Ferrari  a écrit :

> On Thu, Jul 15, 2021 at 7:27 PM Francisco Olarte 
> wrote:
> > It would probably complicate it, and dumping a whole cluster using
> > something like pg_dumpall -g for the globals plus a loop over the
> > databases using something like pg_dump -Fc ( which I would always
> > recommend over plain sql format ) is just a ( little complex ) one
> > liner or a 10 line script, probably not worth the scarce developer /
> > maintainer brain cycles.
>
> I always find the 502.pgsql FreeBSD backup script illuminating in this
> sense: <
> https://svnweb.freebsd.org/ports/head/databases/postgresql10-server/files/502.pgsql.in?view=markup#l44
> >.
> I used it as a base for my "loop and backup" scripts.
>
>
I like the simple pg_back tool ( https://github.com/orgrim/pg_back ). I
much prefer the bash version, but both (bash 1.x version, and go 2.x
version) are working great.


-- 
Guillaume.

Re: pg 13 fatal error message mentionning "standby_mode"

[Guillaume Lelarge]

Le mar. 8 juin 2021 à 13:15, Guillaume Lelarge  a
écrit :

> Hi,
>
> Le mar. 8 juin 2021 à 12:11, Phil Florent  a
> écrit :
>
>> Hi,
>>
>> I had forgotten to include a restore_command and my standby database did
>> not start (expected behavior) but the error message puzzled me :
>>
>> select version();
>>
>>   version
>>
>>
>> ---
>>
>> PostgreSQL 13.2 (Debian 13.2-1) on x86_64-pc-linux-gnu, compiled by gcc
>> (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
>>
>> (1 ligne)
>>
>>
>>
>> juin 08 11:50:26 sv-t-vtl-bas06 postgresql@13-echange_p[2921]:
>> 2021-06-08 11:50:26.666 CEST [2927] *FATAL:  doit spécifier une
>> restore_command quand standby_mode n'est pas activé*
>>
>> It's in French but it means I have to specify restore_command if
>> standby_mode is not activated.
>>
>> It's a pg 13 cluster and standby_mode parameter was removed with pg 12+ ?
>> Perhaps the French translation is wrong ?
>>
>>
> The translation is wrong. The original message is "must specify
> restore_command when standby mode is not enabled". There's no underscore
> between standby and mode, so it isn't the parameter. I must have missed
> that change during the translation. I'll fix it right away.
>
>
Done.


-- 
Guillaume.

Re: pg 13 fatal error message mentionning "standby_mode"

[Guillaume Lelarge]

Hi,

Le mar. 8 juin 2021 à 12:11, Phil Florent  a
écrit :

> Hi,
>
> I had forgotten to include a restore_command and my standby database did
> not start (expected behavior) but the error message puzzled me :
>
> select version();
>
>   version
>
>
> ---
>
> PostgreSQL 13.2 (Debian 13.2-1) on x86_64-pc-linux-gnu, compiled by gcc
> (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
>
> (1 ligne)
>
>
>
> juin 08 11:50:26 sv-t-vtl-bas06 postgresql@13-echange_p[2921]: 2021-06-08
> 11:50:26.666 CEST [2927] *FATAL:  doit spécifier une restore_command
> quand standby_mode n'est pas activé*
>
> It's in French but it means I have to specify restore_command if
> standby_mode is not activated.
>
> It's a pg 13 cluster and standby_mode parameter was removed with pg 12+ ?
> Perhaps the French translation is wrong ?
>
>
The translation is wrong. The original message is "must specify
restore_command when standby mode is not enabled". There's no underscore
between standby and mode, so it isn't the parameter. I must have missed
that change during the translation. I'll fix it right away.

Thanks.

Regards.


-- 
Guillaume.

Re: DB size

[Guillaume Lelarge]

Hi,

Le lun. 26 avr. 2021 à 22:59,  a écrit :

>
> - Mensagem original -
> > De: "Alvaro Herrera" 
> > Para: "luis.roberto" 
> > Cc: "pgsql-general" 
> > Enviadas: Segunda-feira, 26 de abril de 2021 17:45:34
> > Assunto: Re: DB size
>
> > I would guess that there are leftover files because of those crashes you
> > mentioned. You can probably look for files in the database subdir in
> > the data directory that do not appear in the pg_class.relfilenode
> > listing for the database.
>
> > --
> > Álvaro Herrera 39°49'30"S 73°17'W
> >  really, I see PHP as like a strange amalgamation of C, Perl,
> Shell
> >  inflex: you know that "amalgam" means "mixture with mercury",
> > more or less, right?
> >  i.e., "deadly poison"
>
>
> Thanks Alvaro!
>
> That's what I read in an old thread, back in the 8.3 days... Can you point
> me in the right direction as to where sould I look and how do I know which
> files exactly are safe to remove?
>
>
pg_orphaned is probably what you need. See
https://github.com/bdrouvot/pg_orphaned for details.


-- 
Guillaume.

Re: Question about contrib

[Guillaume Lelarge]

Le lun. 19 avr. 2021 à 10:43, Condor  a écrit :

> On 19-04-2021 11:39, Guillaume Lelarge wrote:
> > Hi,
> >
> > Le lun. 19 avr. 2021 à 09:43, Condor  a écrit :
> >
> >> Hello,
> >>
> >> I have a question about contrib directory. I know the projects there
> >> is
> >> not official supported by PostgreSQL development team
> >
> > Contrib modules (as in the contrib subdirectory) are officially
> > supported.
> >
> > --
> >
> > Guillaume.
>
> Aha my mistake, sorry. Why is not in main tree then ?
>
>
Because they aren't interesting for everyone. Moreover, some are more dev
tools, than DBA tools. So better to pick the tools you need.


-- 
Guillaume.

Re: Question about contrib

[Guillaume Lelarge]

Hi,

Le lun. 19 avr. 2021 à 09:43, Condor  a écrit :

>
> Hello,
>
> I have a question about contrib directory. I know the projects there is
> not official supported by PostgreSQL development team


Contrib modules (as in the contrib subdirectory) are officially supported.


-- 
Guillaume.

Re: Upgrading from 11 to 13

[Guillaume Lelarge]

Hi,

Le mar. 30 mars 2021 à 16:10, Susan Joseph  a
écrit :

> I am currently using PostgreSQL 11.2 and would like to try and upgrade it
> to the latest version 13.  Can I go straight from 11 to 13 or do I need to
> upgrade to 12 first and then to 13?
>

You can go straight to 13.

Regards.

Re: ERROR: could not start WAL streaming: ERROR: replication slot "XXX" does not exist

[Guillaume Lelarge]

Le dim. 28 mars 2021 à 11:53, FOUTE K. Jaurès  a
écrit :

> Curiously the slot is not dropped.
>
>
You didn't say the name of the replication slot. Does it have upper case?
That's probably the biggest issue with naming objects with PostgreSQL.

Le sam. 27 mars 2021 à 20:31, Atul Kumar  a écrit :
>
>> As per your error, it seems replication slot has been dropped.
>>
>> On Saturday, March 27, 2021, FOUTE K. Jaurès 
>> wrote:
>>
>>> Hello EveryOne,
>>>
>>> How can I solve Issue. ???
>>>
>>>
>>>
>>> *ERROR:  could not start WAL streaming: ERROR:  replication slot "XXX"
>>> does not exist2021-03-27 11:48:33.012 WAT [1090] LOG:  background worker
>>> "logical replication worker" (PID 8458) exited with exit code 12021-03-27
>>> 11:48:38.019 WAT [8461] LOG:  logical replication apply worker for
>>> subscription "XXX has started*
>>>
>>> All working fine a few days ago but this morning I have this issue.
>>>
>>> --
>>> Jaurès FOUTE
>>>
>>
>
> --
> Jaurès FOUTE
>


-- 
Guillaume.

Re: How to recover data from 9.3 data directory

[Guillaume Lelarge]

Le sam. 13 mars 2021 à 15:18, Andrus  a écrit :

> Should I create virtual machine and install Linux with 9.3 into it or is
>> there simpler solution?
>>
> that's the only option, unless you already have some compatible server
> around.
>
> I have new Debian virtual machine with Postgres 12  .
>
> Debian does not offer to install Postgres 9.3 into it. How to install
> postgres 9.3 in new Debian ?
>

You'll have to compile it.


-- 
Guillaume.

Re: count(*) vs count(id)

[Guillaume Lelarge]

Le mar. 2 févr. 2021 à 02:14, Matt Zagrabelny  a écrit :

>
>
> On Mon, Feb 1, 2021 at 6:35 PM Tom Lane  wrote:
>
>> Matt Zagrabelny  writes:
>> > On Mon, Feb 1, 2021 at 5:57 PM Rob Sargent 
>> wrote:
>> >> You got one null from count(*) likely.
>>
>> > What is count(*) counting then? I thought it was rows.
>>
>> Yeah, but count(id) only counts rows where id isn't null.
>>
>
> I guess I'm still not understanding it...
>
> I don't have any rows where id is null:
>
> $ select count(*) from call_records where id is null;
>  count
> ═══
>  0
> (1 row)
>
> Time: 0.834 ms
> $
>
> select count(id) from call_records where id is null;
>  count
> ═══
>  0
> (1 row)
>
> Time: 0.673 ms
>
> Which field is count(*) counting if it is counting nulls?
>

You're doing a left join, so I guess there's no row where
call_records.timestamp::date = 2020-08-30. That would result with a NULL id.

Guillaume

Re: CLUSTER, VACUUM, and TABLESPACEs (oh my)

[Guillaume Lelarge]

Le lun. 25 janv. 2021 à 03:27, Demitri Muna  a
écrit :

>
> > On Jan 24, 2021, at 9:03 PM, Guillaume Lelarge 
> wrote:
> >
> >> VACUUM FULL unclusters the table??
> >
> > It will rebuild the table without sorting the data according to the
> index used with CLUSTER (without any sorting actually).
>
> Thank you for the clarification; that’s very helpful. For the case above.
> If I CLUSTER a table, add/delete no rows, then VACUUM, will the existing
> order remain or be changed?
>

As long as you don't insert or update any rows, the order should remain
even after a VACUUM FULL.

Re: CLUSTER, VACUUM, and TABLESPACEs (oh my)

[Guillaume Lelarge]

Le lun. 25 janv. 2021 à 02:54, Ron  a écrit :

> On 1/24/21 7:50 PM, Guillaume Lelarge wrote:
>
> Hi,
>
> Le lun. 25 janv. 2021 à 01:38, Demitri Muna  a
> écrit :
>
> [snip]
>
>
>> * If I have previously run a CLUSTER command on a table, will future
>> VACUUM FULL commands rewrite the table in the order specified in the
>> previous CLUSTER?
>>
>
> No, you still need CLUSTER.
>
>
> VACUUM FULL unclusters the table??
>

It will rebuild the table without sorting the data according to the index
used with CLUSTER (without any sorting actually).

>

Re: CLUSTER, VACUUM, and TABLESPACEs (oh my)

[Guillaume Lelarge]

Hi,

Le lun. 25 janv. 2021 à 01:38, Demitri Muna  a
écrit :

> Hi,
>
> I would like to request a little clarification on the CLUSTER and VACUUM
> commands. My use case here (partially) is when my disk runs out of space
> and I want to move a table to a newly created tablespace. These questions
> somewhat overlap. Let’s say I am starting with a table that is not
> CLUSTERed on a given index, but I want it to be.
>
> * If I run “CLUSTER table USING idx” on a table, is VACUUM FULL
> required/useful afterwards, or should I assume that the cluster operation
> did the equivalent of a VACUUM FULL?
>

The cluster operation is a VACUUM FULL with a sort step, so you don't need
a VACUUM FULL after a CLUSTER.


> * If I have previously run a CLUSTER command on a table, will future
> VACUUM FULL commands rewrite the table in the order specified in the
> previous CLUSTER?
>

No, you still need CLUSTER.


> * If I want to move a table to a new tablespace, is it possible to
> CLUSTER+VACUUM in the same step since the whole table will be rewritten
> anyway? This would be very useful in low-disk space scenarios. I did find
> this answer, but it’s dated several years ago and was hoping for something
> better supported. https://dba.stackexchange.com/a/87457/121020


No, but this is something being worked on. See
https://commitfest.postgresql.org/31/2269/ for details.


> The documentation is somewhat silent on these details, so I thought I’d
> ask here. Right now I move a table to a new tablespace, cluster on an
> index, and then do a full vacuum which results in three full copies of the
> table being written, which seems less than optimal where one should only be
> needed as far as I understand things.
>
> Cheers,
> Demitri
>
>

Re: Deleting takes days, should I add some index?

[Guillaume Lelarge]

Le ven. 27 nov. 2020 à 16:05, Alvaro Herrera  a
écrit :

> On 2020-Nov-27, Alexander Farber wrote:
>
> > Referenced by:
> > TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid)
> > REFERENCES words_games(gid) ON DELETE CASCADE
> > TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY
> (gid)
> > REFERENCES words_games(gid) ON DELETE CASCADE
> > TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
> > (gid) REFERENCES words_games(gid) ON DELETE CASCADE
>
> Make sure you have indexes on the gid columns of these tables.  Delete
> needs to scan them in order to find the rows that are cascaded to.
>
>
An index on words_games(finished) and words_moves(played) would help too.


-- 
Guillaume.

Re: Cluster and Vacuum Full

[Guillaume Lelarge]

Le lun. 5 oct. 2020 à 12:22, PegoraroF10  a écrit :

> I have tables which are Master Detail and usually my program loads all
> detail
> records of a master record. So I configured CLUSTER on all those detail
> tables to use an index which is the relation with master table. With that I
> can load less records to Shared Buffers because all detaild records are on
> sequencial pages, that´s fine.
>
> I know that to have this working I have to periodically run CLUSTER, ok
> too.
>
> But today instead of CLUSTER I just ran VACUUM FULL. How vacuum full
> recreates entirelly that table, like cluster does, I thought it would use
> that index too, but didn´t. I didn´t see what sequence of records it did
> but
> was not the one I´ve configured with cluster.
>
> Is that expected or Vacuum should obbey cluster configuration ?
>
>
Only CLUSTER obeys cluster configuration. VACUUM FULL in particular doesn't.


-- 
Guillaume.

Re: Need explanation on index size

[Guillaume Lelarge]

Hi,

Le jeu. 24 sept. 2020 à 15:55, Guillaume Luchet  a
écrit :

> Hi,
>
> I’m facing of a comportement I don’t understand on indexes, here a quick
> example to reproduce my problem
>
>
> test=# select version();
>  version
>
>
> --
>  PostgreSQL 12.4 (Debian 12.4-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled
> by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
> (1 row)
>
> test=# create table plop (col_a int, col_b int, col_c int);
> CREATE TABLE
>
> test=# create unique index on plop (col_a);
> CREATE INDEX
>
> test=# create index on plop(col_b);
> CREATE INDEX
>
> test=# insert into plop (col_a, col_b) select generate_series(1, 1),
> generate_series(1, 1);
> INSERT 0 1
>
> test=# SELECT schemaname, tablename,
>  pg_size_pretty(SIZE) AS size_pretty,
>  pg_size_pretty(total_size) AS total_size_pretty
> FROM (SELECT *, pg_relation_size(quote_ident(schemaname) || '.' ||
> quote_ident(tablename)) AS SIZE,
> pg_total_relation_size(quote_ident(schemaname) || '.' ||
> quote_ident(tablename)) AS total_size
>   FROM pg_tables) AS TABLES where tablename = 'plop';
>  schemaname | tablename | size_pretty | total_size_pretty
> +---+-+---
>  public | plop  | 360 kB  | 864 kB
> (1 row)
>
> test=# update plop set col_c = floor(random() * 10 + 1)::int;
> UPDATE 1
>
> test=# SELECT schemaname, tablename,
>  pg_size_pretty(SIZE) AS size_pretty,
>  pg_size_pretty(total_size) AS total_size_pretty
> FROM (SELECT *, pg_relation_size(quote_ident(schemaname) || '.' ||
> quote_ident(tablename)) AS SIZE,
> pg_total_relation_size(quote_ident(schemaname) || '.' ||
> quote_ident(tablename)) AS total_size
>   FROM pg_tables) AS TABLES where tablename = 'plop';
>  schemaname | tablename | size_pretty | total_size_pretty
> +---+-+---
>  public | plop  | 792 kB  | 2160 kB
> (1 row)
>
> test=# reindex table plop;
> REINDEX
>
> test=# SELECT schemaname, tablename,
>  pg_size_pretty(SIZE) AS size_pretty,
>  pg_size_pretty(total_size) AS total_size_pretty
> FROM (SELECT *, pg_relation_size(quote_ident(schemaname) || '.' ||
> quote_ident(tablename)) AS SIZE,
> pg_total_relation_size(quote_ident(schemaname) || '.' ||
> quote_ident(tablename)) AS total_size
>   FROM pg_tables) AS TABLES where tablename = 'plop';
>  schemaname | tablename | size_pretty | total_size_pretty
> +---+-+---
>  public | plop  | 792 kB  | 1304 kB
> (1 row)
>
> I don’t understand why after the update where I only update a non indexed
> column the indexes size is growing. Is it something someone can explain ?
>
>
Every tuple is now on a different location on the table (remember that
update in PostgreSQL is more something like delete+insert). So even if the
value of the column doesn't change, its tuple location changes, so the
index needs to be updated to reflect that change.


-- 
Guillaume.

Re: Error in Table Creation

[Guillaume Lelarge]

Le jeu. 25 juin 2020 à 10:59, Rajnish Vishwakarma <
rajnish.nationfi...@gmail.com> a écrit :

> I am creating dynamically table in PostgreSQL using psycopg2 by passing
> the below 2 strings as column names:
>
>
> 'BAF7_X_X_During_soaking-__Temperature__difference_coil_to_coil_with_metal_temp_TC_load_in_PA_load'
>
> and
>
>
> 'BAF7_X_X_During_soaking-__Temperature__difference_coil_to_coil_with_metal_temp_TC_load__in_TA_load'
>
> And the above column names are not same ( both are different columns ) and
> both the columns has string same till   
> *'BAF7_X_X_During_soaking-__Temperature__difference_coil_to_coil_with_metal_temp_TC_load__
> .*
>
> but i am getting errors as
>
> Error:
> Traceback (most recent call last):
>   File 
> "C:/Users/Administrator/PycharmProjects/untitled/table_creation_with_HDA_Data.py",
>  line 131, in 
> cursor.execute(sqlCreateTable)
> psycopg2.errors.DuplicateColumn: column 
> "BAF7_X_X_During_soaking-__Temperature__difference_coil_to_coil_" specified 
> more than once
>
> The above columns are of type TEXT ...also it may be Numeric Type in
> future.
>
> Require assistance from Postgres team on the above error.
>

Object names in PostgreSQL are limited to 63 characters. If you put more
characters, it will silently truncate them to 63 characters. In your case,
they are truncated, and the truncated strings end up being the same. So,
use less characters for your objects' names.

Re: Lock Postgres account after X number of failed logins?

[Guillaume Lelarge]

Le mer. 6 mai 2020 à 04:18, Christian Ramseyer  a écrit :

>
>
> On 05.05.20 16:13, Wolff, Ken L wrote:
> > Hi, everyone.  Wondering if there’s a way in PostgreSQL to automatically
> > lock accounts after a number of failed logins (a security requirement
> > for my organization).
> >
> > Locking accounts after X number of failed logins is an excellent way to
> > defeat brute force attacks, so I’m just wondering if there’s a way to do
> > this, other than the aforementioned hook.
> >
> >
>
> Hi Ken
>
> This doesn't seem mentioned in other replies so far: a very "unixy"
> approach to bolt this feature onto almost any Linux server process is
> the fail2ban (https://github.com/fail2ban/fail2ban) utility. This is a
> daemon that reads arbitrary logfiles, and then triggers an action if
> some failure condition is seen a number of times.
>
> Typically this will scan the logfile for an IP and on failure add a
> temporary firewall rule to block the source, but all of this is
> configurable. So in your case you can lock the account instead, and then
> decide if you want automatic unlocking after a while, if you want to
> drop the IP that tried to login additionally on the firewall as well, etc.
>
> Here is a quick, rough example with still some blanks to fill in - I put
> it on github for readability:
> 
>
> The main blanks are in the postgres-action.conf section. The called
> scripts in /usr/local/bin would need to be written. It can be as simple
> as "psql -c alter role xxx nologin", but you might add some features
> like connecting to the primary server if fail2ban triggered on the
> standby. Also I'm not sure if setting nologin is the best way to disable
> an account, but I'm sure somebody on here could tell you.
>
>
I already knew about fail2ban, but didn't know it could be set up this way.
That's pretty impressive. I've just finished testing your config files, and
it works really well (well, when you finally get rid of the selinux
permission errors :) ). Anyway, thanks a lot for sharing this.


-- 
Guillaume.

Re: avoid WAL for refresh of materialized view

[Guillaume Lelarge]

Le mar. 24 mars 2020 à 17:00, Remund Alain  a
écrit :

> Hi all
>
>
>
> We have PostgreSql 9.6 running and started to work with materialized
> views. To refresh the materialized views, we set up a cron job that
> refreshes the materialized views on a fix schedule.
>
> Since our materialized views cache quite some data, we noticed a
> considerable increase in WAL files. It seems, that every refresh of a
> materialized view is logged in the WAL.
>
>
>
> We tried to figure out how we can alter the materialized view to set it to
> "UNLOGGED" but this does not seem possible.
>
> --> "alter materialized view xyz set UNLOGGED;" leads to "ERROR:  "xyz" is
> not a table, SQL state: 42809"
>
>
>
> Is there another way to avoid logging a refresh of a materialized view in
> the WAL?
>
>
>

As you say, there is no unlogged materialized view. So, no, it will always
log to the WAL during refresh.

>

-- 
Guillaume.

Re: POLL: Adding transaction status to default psql prompt

[Guillaume Lelarge]

Le jeu. 6 févr. 2020 à 03:55, Vik Fearing  a
écrit :

> Hello,
>
> I proposed a patch to add %x to PROMPT1 and PROMPT2 by default in psql.
>
> The effect of this is:
>
> - nothing at all when not in a transaction,
>

Too bad it doesn't add a space, so that we still have the same space used
byt the prompt whatever the status of the transaction. That's probably the
only thing that bugs me with %x.

- adding a '*' when in a transaction or a '!' when in an aborted
>   transaction.
>
> Before making a change to a long-time default, a poll in this group was
> requested.
>
> Please answer +1 if you want or don't mind seeing transaction status by
> default in psql or -1 if you would prefer to keep the current default.
>
> Thanks!
>
> +1 from me.
>

+1 from me as well. Actually, thanks to you, I added it to my .psqlrc file
in the meantime. But it would be very interesting when I work on a
customer's server.


-- 
Guillaume.

Re: How to reset a server error '25P02 in_failed_sql_transaction'

[Guillaume Lelarge]

Le mar. 31 déc. 2019 à 06:55, Matthias Apitz  a écrit :

>
> Hello,
>
> Due to a wrong human input in the GUI of our application our
> application server, from the point of view of the PostgreSQL server it
> is the PostgreSQL client, issues a broken ESQL/C command to the PostgreSQL
> server, here from our own debug logging the command and the error
> message of the server:
>
>
> posDebug: [27.12.2019 15:20:59:043] stmt: SELECT ctid, * from titel_datum
> WHERE desk>='*2019' AND feldnr IN ( 2 )   ORDER BY desk ASC , feldnr ASC
> posDebug: [27.12.2019 15:20:59:043] ESQL: PREPARE sid_titel_datum  FROM
> :select_anw;
>  sqlca 
> sqlcode: -400
> sqlerrm.sqlerrml: 61
> sqlerrm.sqlerrmc: invalid input syntax for type date: »*2019« on line 918
> ...
>
> All subsequent correct (SELECT ...) statements get rejected with, for
> example:
>
>
> ...
> posDebug: [27.12.2019 15:20:59:044] stmt: SELECT ctid, * from titel_datum
> WHERE desk>='31.12.1900' AND feldnr IN ( 2 )   ORDER BY desk ASC , feldnr
> ASC
> posDebug: [27.12.2019 15:20:59:044] ESQL: PREPARE sid_titel_datum  FROM
> :select_anw;
>  sqlca 
> sqlcode: -400
> sqlerrm.sqlerrml: 105
> sqlerrm.sqlerrmc: current transaction is aborted, commands ignored until
> end of transaction block on line 918
> sqlerrd: 0 0 0 0 0 0
> sqlwarn: 0 0 0 0 0 0 0 0
> sqlstate: 25P02
> posSqlError===
> ...
>
> Note: we are not in some kind TRANSACTION block, like 'EXEC SQL BEGIN
> TRANSACTION;'
>
> What is the correct way to abort the "transaction" as requested by the PG
> server to return to normal operations?
>
>
You need to issue a ROLLBACK. then you'll be able to open another
transaction.


-- 
Guillaume.

Re: Finding out about the dates of table modification

[Guillaume Lelarge]

Le sam. 23 nov. 2019 03:24, Martin Mueller 
a écrit :

>  I've moved happily from MySQL to Postgres but miss one really good
> feature of MYSQL: the table of tables that let you use SQL queries to find
> out metadata about your table. Thus looking at the table of tables and
> sorting it by last change, lets you quickly look at the most recently
> modified table. Which is really useful if you have a bad memory, as I do,
> and can't remember the name of a particular table that I worked on last
> Wednesday.
>
> Are those capabilities hidden somewhere in Postgres?  There isn't an
> obvious section in the documentation. At least I can't find it.
>

No, it doesn't exist.

Re: A question about user atributes

[Guillaume Lelarge]

First, please reply to the list, not me specifically.

Le ven. 22 nov. 2019 à 14:51, stan  a écrit :

> On Fri, Nov 22, 2019 at 01:58:11PM +0100, Guillaume Lelarge wrote:
> > Hi,
> >
> > Le ven. 22 nov. 2019 ?? 13:51, stan  a ??crit :
> >
> > > I am trting to do something, and it ias not working as I think it
> should.
> > >
> > > Consider:
> > >
> > > onnected to a database called stan as stan
> > >
> > > /dt reports
> > >
> > > List of relations
> > > Schema |   Name   | Type  | Owner
> > >  +--+---+---
> > >  ica| biz_constants| table | stan
> > >  ica| bom_item | table | stan
> > >  ica| costing_unit | table | stan
> > >  ica| customer | table | stan
> > >  ica| employee | table | stan
> > >
> > >  Connect as user postgres to database stan and execute:
> > >
> > >  grant all ON ALL TABLES IN SCHEMA ica to harec ;
> > >
> > >  Connect to database stan as user harec
> > >
> > >  stan=> \dt
> > >  Did not find any relations.
> > >  stan=> \c
> > >  psql (12.1 (Debian 12.1-1.pgdg100+1), server 11.6 (Debian
> > >  11.6-1.pgdg100+1))
> > >  You are now connected to database "stan" as user "harec".
> > >  stan=>
> > >
> > >  What am I doing wrong?
> > >
> > >
> > Might be a wrong search_path or maybe your harec user doesn't have USAGE
> > right on the ica schema.
> >
>
> Well,
>
> [local] stan@stan=# \dn
>   List of schemas
> Name  |  Owner
> +--
>  ica| stan
>  public | postgres
>  stan   | stan
>
> [local] stan@stan=# grant usage on ica to harec ;
> ERROR:  relation "ica" does not exist
>
> Is this incorect syntaxe?
>
>
Yes, it is incorrect. You should tell PostgreSQL you're talking about a
schema, hence:
GRANT USAGE ON SCHEMA ica TO harec;

See https://www.postgresql.org/docs/12/sql-grant.html for details.


-- 
Guillaume.

Re: A question about user atributes

[Guillaume Lelarge]

Hi,

Le ven. 22 nov. 2019 à 13:51, stan  a écrit :

> I am trting to do something, and it ias not working as I think it should.
>
> Consider:
>
> onnected to a database called stan as stan
>
> /dt reports
>
> List of relations
> Schema |   Name   | Type  | Owner
>  +--+---+---
>  ica| biz_constants| table | stan
>  ica| bom_item | table | stan
>  ica| costing_unit | table | stan
>  ica| customer | table | stan
>  ica| employee | table | stan
>
>  Connect as user postgres to database stan and execute:
>
>  grant all ON ALL TABLES IN SCHEMA ica to harec ;
>
>  Connect to database stan as user harec
>
>  stan=> \dt
>  Did not find any relations.
>  stan=> \c
>  psql (12.1 (Debian 12.1-1.pgdg100+1), server 11.6 (Debian
>  11.6-1.pgdg100+1))
>  You are now connected to database "stan" as user "harec".
>  stan=>
>
>  What am I doing wrong?
>
>
Might be a wrong search_path or maybe your harec user doesn't have USAGE
right on the ica schema.


-- 
Guillaume.

Re: Execute a function through fdw

[Guillaume Lelarge]

Le ven. 18 oct. 2019 à 11:51, Patrick FICHE  a
écrit :

> Hello,
>
>
>
> Is it possible to execute a function located on a server accessed through
> Postgres fdw.
>
> This function returns a TABLE structure.
>
>
>
> I have mapped rmt_schema and there is a function called Get_Tables in this
> schema.
>
> I would like to execute something like :
>
> SELECT * FROM rmt_schema.Get_Tables();
>
>
>
> Or is it required to create a local function that will access to remote
> tables in order to achieve the same result ?
>

It's probably easier to create a view on the remote server, and access it
as a foreign table on the local server.

>

Re: logging "raise" to file

[Guillaume Lelarge]

Hi,

Le dim. 28 juil. 2019 à 12:32,  a écrit :

> Hi,
>
> is there a way to log output from "raise ..." to a local file? \o file
> does not work (for me).
>

No, RAISE messages can only go to log files. You would need to call a
function that could write to a file (though without calling RAISE).


-- 
Guillaume.

Re: One way replication in PostgreSQL

[Guillaume Lelarge]

Le mar. 4 juin 2019 à 09:03, PALAYRET Jacques  a
écrit :

> Hello,
>
> Thanks a lot for the suggested solutions.
>
> So, I can use WAL-shipping replication from Primary to the Secundary
> server, but it's only for full replication.
>
>
Yes.

Let's call " P " the provider/primary/master  and " S " the
> subscriber/secundary/slave one.
> For partial replication (not all the tables), the solutions should use a
> third (intermediate / middle) server which could have both ways flow with
> the server P but only one way flow towards the server S.
> For example, a logical replication (pglogical or logical Postgresql
> replication) between server P and the middle server and then a WAL-shipping
> replication between middle server and server S.
> Is that right ?
>
>
That could work.

About the " FDW " solution in " an external server " (a middle one), is it
> possible to integrate the FDW in the P server to avoid the " external
> server " ?
>
>
Not sure I understand where you're going here.

=> What about the trigger-based replication systems like Slony or Londiste
> ; is it really necessary to have a connection or flow from the server S
> towards the server P ?
>
>
As far as I remember, with Slony, P must be able to connect to S, and S
must be able to connect to P.


-- 
Guillaume.
Dalibo.

Re: Weird behaviour of ROLLUP/GROUPING

[Guillaume Lelarge]

Le jeu. 17 janv. 2019 à 08:27, Andrew Gierth 
a écrit :

> >>>>> "Guillaume" == Guillaume Lelarge  writes:
>
>  >> I will see about fixing this, somehow.
>
>  Guillaume> Thanks a lot.
>
> I've committed a fix (to all supported branches, since this bug actually
> precedes the addition of GROUPING SETS and can be triggered with a
> simple GROUP BY if you try hard enough). The regression test says it
> works now, but it'd be good if you could try it again on REL_11_STABLE
> (at commit e74d8c5085 or later) to check that it fixes your case.
>
>
I checked on REL9_6_STABLE and REL_11_STABLE, and it works great. Thank a
lot for the quick fix!


-- 
Guillaume.

Re: Weird behaviour of ROLLUP/GROUPING

[Guillaume Lelarge]

Le mer. 16 janv. 2019 à 17:40, Andrew Gierth 
a écrit :

> >>>>> "Andrew" == Andrew Gierth  writes:
> >>>>> "Guillaume" == Guillaume Lelarge  writes:
>
>  Guillaume> CASE grouping(to_char(b, 'MM'))
>
>  Guillaume> ERROR:  arguments to GROUPING must be grouping expressions of
> the
>  Guillaume> associated query level
>  Guillaume> LINE 3: CASE grouping(to_char(b, 'MM')) WHEN 1 THEN 'some
> date' ...
>
>  Guillaume> AFAICT, both queries should behave the same, though their
>  Guillaume> actual behaviours are quite opposite. Working fine for the
>  Guillaume> first, erroring out on the second.
>
>  Guillaume> Does anyone has any idea what's going on here?
>
>  Andrew> Not yet. But I will find out, since it seems to be a bug.
>
> It is definitely a bug, to do with assignment of collations. It
> specifically happens when you use GROUPING which contains any
> subexpression of a collatable type, inside a CASE expr WHEN clause,
> since that assigns collations to the expression much earlier in parsing
> than the rest of the query, so the code that validates GROUPING ends up
> trying to compare an expression which has had collations assigned to it
> to one which has not, and so it thinks they differ.
>
> I will see about fixing this, somehow.
>
>
Thanks a lot.


-- 
Guillaume.

Weird behaviour of ROLLUP/GROUPING

[Guillaume Lelarge]

Hi,

One of my customers found something quite weird on his 9.6 cluster. Here is
a quick demo showing the issue:

-- quick demo table
CREATE TABLE t1 (a integer, b timestamp, c integer);

-- a working query
SELECT
  CASE grouping(a) WHEN 1 THEN 'some text' ELSE a::text END AS new_a,
  CASE WHEN grouping(to_char(b, 'MM')) = 1
THEN 'some date'
ELSE to_char(b, 'MM') END AS new_b,
  sum(c)
FROM t1
GROUP BY ROLLUP(a, to_char(b,'MM'));

-- the non-working query
SELECT
  CASE grouping(a) WHEN 1 THEN 'some text' ELSE a::text END AS new_a,
  CASE grouping(to_char(b, 'MM'))
WHEN 1 THEN 'some date'
ELSE to_char(b, 'MM') END AS new_b,
  sum(c)
FROM t1
GROUP BY ROLLUP(a, to_char(b,'MM'));
ERROR:  arguments to GROUPING must be grouping expressions of the
associated query level
LINE 3: CASE grouping(to_char(b, 'MM')) WHEN 1 THEN 'some date' ...
  ^

AFAICT, both queries should behave the same, though their actual behaviours
are quite opposite. Working fine for the first, erroring out on the second.

Does anyone has any idea what's going on here?

BTW, it shows the same issue on 11.1 (actually HEAD on REL_11_STABLE).

Thanks.

Regards.


-- 
Guillaume.

Re: Installing pg_buffercache (was Re: shared_buffers on Big RAM systems)

[Guillaume Lelarge]

Le ven. 14 déc. 2018 à 07:00, Ron  a écrit :

> On 12/13/2018 08:25 PM, Rene Romero Benavides wrote:
> > This topic seems to be always open to discussion. In my opinion, it
> > depends on how big your work dataset is, there's no use in sizing
> > shared_buffers beyond that size. I think, the most reasonable thing is
> > analyzing each case as proposed here:
> >
> https://www.keithf4.com/a-large-database-does-not-mean-large-shared_buffers/
>
> https://www.postgresql.org/docs/current/pgbuffercache.html
>
> Is this an extension or a shared preload library?  The documentation
> doesn't
> specify.
>
>
It's an extension.


-- 
Guillaume.

Re: Postgres 8.3 Grant all on database or schema

[Guillaume Lelarge]

Hi,

Le mer. 7 nov. 2018 à 09:59, Saikumar  a écrit :

> Dear Sir,
>
>
>
> I am quite new the Postgre DB and working as DBA.
>
>
>
> I wanted to set the grant all permissions on the database to the role.
>
>
>
> Whatever the user assign to this role should inherits the same permissions
> on the database tables. To achieve this scenarios, I have executed below
> steps.
>
>
>
>   GRANT ALL ON database RM to RM_ADMIN_ROLE;
>
> create user junkai;
>
> ALTER ROLE junkai WITH LOGIN;
>
> grant RM_ADMIN_ROLE to junkai;
>
>
>
> I login using junkai, and try to access the tables, its giving the error
> as permission denied error.
>
>
>
> If I grant the same access on table level, it’s working. I have hundreds
> of tables in the schema. I can’t grant the permissions on table level.
>
>
>
> I have check in the internet, all suggesting same as above. When I run the
> above commands, it’s not throwing any error all says granted. When I do
> select, It’s not allowing to fetch the data.
>
>
>
> Could you please help why it’s not working as expected?
>
>
>

It's working as expected. When you GRANT ALL on a database, you give this
role CREATE, TEMP and CONNECT privilege to this database (IOW, all
privileges related to a database object). It doesn't give any privilege to
objects inside this database. On a 8.3, you'll have to give privileges
table per table. On a more recent release, you would be able to do it
schema by schema. Usually, people use scripts to do this when they have a
lot of tables.

Regards.


-- 
Guillaume.

Re: Weird behaviour of the planner

[Guillaume Lelarge]

2018-08-01 16:59 GMT+02:00 David Rowley :

> On 2 August 2018 at 02:48, Guillaume Lelarge 
> wrote:
> > EXPLAIN (ANALYZE) SELECT DISTINCT * FROM gleu2;
> >
> >QUERY PLAN
> > 
> -
> >  Unique  (cost=12005.97..12049.20 rows=1 width=1430) (actual
> > time=20055.294..20323.348 rows=1 loops=1)
> >->  Sort  (cost=12005.97..12006.30 rows=132 width=1430) (actual
> > time=20055.290..20105.738 rows=6 loops=1)
> >  Sort Key: (... 130 columns ...)
> >  Sort Method: external sort  Disk: 84464kB
> >  ->  Seq Scan on gleu2  (cost=0.00..12001.32 rows=132 width=1430)
> > (actual time=0.109..114.142 rows=6 loops=1)
> >  Planning time: 10.012 ms
> >  Execution time: 20337.854 ms
> > (7 rows)
> >
> > That looks quite good. The total cost is 12049, so I expect this plan to
> > have the smaller cost as it's the choosen plan. Now, I'm disabling Sort,
> and
> > here is what I get:
> >
> > SET enable_sort TO off;
> > EXPLAIN (ANALYZE) SELECT DISTINCT * FROM gleu2;
> >
> >QUERY PLAN
> > 
> -
> >  HashAggregate  (cost=12044.22..12044.23 rows=1 width=1430) (actual
> > time=508.342..508.343 rows=1 loops=1)
> >Group Key: (... 130 columns ...)
> >->  Seq Scan on gleu2  (cost=0.00..12001.32 rows=132 width=1430)
> (actual
> > time=0.036..57.088 rows=6 loops=1)
> >  Planning time: 1.335 ms
> >  Execution time: 508.529 ms
> > (5 rows)
>
> When the planner adds a new path it compares the cost not exactly, but
> with a 'fuzz factor'.  It's very likely that the hashagg path did not
> make it as it was not fuzzily any cheaper than the unique path. By
> default, this fuzz factor is 1%.
>
>
That may definitely be it. There's not much of a difference in the total
costs.

It seems in your case the costs don't quite match reality which is
> quite likely due to the poor row estimates on "gleu2".  Has that table
> been analyzed recently? or is there some reason that auto-vacuum is
> not getting to it?
>
>
This is a small test case of a much bigger query joining a large number of
tables, materialized views, views (calling functions), etc. The actual plan
contains 84 nodes (32 scans, lots of joins, and a few other nodes). The
mis-estimate is to follow what the big query gives me.

There's a bit more reading of what I'm talking about in
> https://github.com/postgres/postgres/blob/master/src/
> backend/optimizer/util/pathnode.c#L141
>
>
I'm gonna read that. Thank you.


-- 
Guillaume.

Weird behaviour of the planner

[Guillaume Lelarge]

Hi,

While working on a slow query of one of our customers, I found this
behaviour which seems quite weird to me. Here it is:

(no changes on any parameter)
EXPLAIN (ANALYZE) SELECT DISTINCT * FROM gleu2;

   QUERY PLAN
-
 Unique  (cost=12005.97..12049.20 rows=1 width=1430) (actual
time=20055.294..20323.348 rows=1 loops=1)
   ->  Sort  (cost=12005.97..12006.30 rows=132 width=1430) (actual
time=20055.290..20105.738 rows=6 loops=1)
 Sort Key: (... 130 columns ...)
 Sort Method: external sort  Disk: 84464kB
 ->  Seq Scan on gleu2  (cost=0.00..12001.32 rows=132 width=1430)
(actual time=0.109..114.142 rows=6 loops=1)
 Planning time: 10.012 ms
 Execution time: 20337.854 ms
(7 rows)

That looks quite good. The total cost is 12049, so I expect this plan to
have the smaller cost as it's the choosen plan. Now, I'm disabling Sort,
and here is what I get:

SET enable_sort TO off;
EXPLAIN (ANALYZE) SELECT DISTINCT * FROM gleu2;

   QUERY PLAN
-
 HashAggregate  (cost=12044.22..12044.23 rows=1 width=1430) (actual
time=508.342..508.343 rows=1 loops=1)
   Group Key: (... 130 columns ...)
   ->  Seq Scan on gleu2  (cost=0.00..12001.32 rows=132 width=1430) (actual
time=0.036..57.088 rows=6 loops=1)
 Planning time: 1.335 ms
 Execution time: 508.529 ms
(5 rows)

I now have a Hash Aggregate. Sounds good, but I do have a smaller total
cost than the former plan. How is it possible that the planner chooses a
plan with a higher cost? (and a much much higher duration, but it can't
obviously know that).

Still working on my customer's issue, I found that it worked great before
commit 3fc6e2d7f5b652b417fa6937c34de2438d60fa9f (Make the upper part of the
planner work by generating and comparing Paths.) and not after. That kind
of patch is way out of my league to be honest :) But before that commit,
the Hash Aggregate is choosen right out of the box. And after that commit,
the Sort is choosen even with its higher cost.

Oh, BTW, yeah, I know. The row estimate is quite off... I'm not asking how
to make my query go faster, I'm just wondering why PostgreSQL chooses a
higher-cost plan :)

Thanks.


-- 
Guillaume.

Re: PG backup check

[Guillaume Lelarge]

2018-07-17 2:35 GMT+02:00 Ravi Krishna :

> Not sure I am following this.  Did Google release this because PG backups
> are not 100% reliable or the data corruption can occur due to hardware
> failure.
>
> http://www.eweek.com/cloud/google-releases-open-source-
> tool-that-checks-postgres-backup-integrity?utm_medium=
> email_campaign=EWK_NL_EP_20180713_STR5L2=450493554=24844166
>

>From what I understand with this Google tool, it has nothing to do with
backups. It just allows you to check data blocks in a PostgreSQL cluster.
Google advice is to run it before taking a backup, but that's about it.


-- 
Guillaume.

Re: Disable TRUST authentication by using ClientAuthentication_hook

[Guillaume Lelarge]

2018-07-13 15:19 GMT+02:00 Fabrízio de Royes Mello 
:

>
> 2018-07-13 9:01 GMT-03:00 Guillaume Lelarge :
>
>> 2018-07-13 13:57 GMT+02:00 :
>>
>>> I’d like to disable the TRUST authentication method for certain servers
>>> where modification of pg_hba.conf and restarting a service is fairly easy
>>> for a number of users.
>>>
>>>
>>>
>>> I looked at this example https://wiki.postgresql.org/im
>>> ages/e/e3/Hooks_in_postgresql.pdf It appears that creating a
>>> ClientAuthentication_hook and call ereport(ERROR) in case that
>>> Port->HbaLine contains TRUST would do the job. Is that right?
>>>
>>>
>>>
>>> I am aware that this would not make the server entirely secure but it
>>> would make it at least a bit more difficult to enter.
>>>
>>>
>>>
>>
>> I'm not sure this is such a good idea. You may need the trust
>> authentication method, for example if you forgot the superuser password.
>> Otherwise, there's good chance you might use the ClientAuthentication hook
>> to do what you want.
>>
>>
>>
> If you're an server admin you can disable the extension (editing
> shared_pre_load_libraries GUC), change password and then enable the
> extension again...
>
>
That would require a restart.

And maybe you can implement a simple way to enable/disable this hook inside
> the extension.
>
>
That looks like a better idea.


-- 
Guillaume.

Re: Disable TRUST authentication by using ClientAuthentication_hook

[Guillaume Lelarge]

2018-07-13 13:57 GMT+02:00 :

> I’d like to disable the TRUST authentication method for certain servers
> where modification of pg_hba.conf and restarting a service is fairly easy
> for a number of users.
>
>
>
> I looked at this example https://wiki.postgresql.org/images/e/e3/Hooks_in_
> postgresql.pdf It appears that creating a ClientAuthentication_hook and
> call ereport(ERROR) in case that Port->HbaLine contains TRUST would do the
> job. Is that right?
>
>
>
> I am aware that this would not make the server entirely secure but it
> would make it at least a bit more difficult to enter.
>
>
>

I'm not sure this is such a good idea. You may need the trust
authentication method, for example if you forgot the superuser password.
Otherwise, there's good chance you might use the ClientAuthentication hook
to do what you want.



-- 
Guillaume.

Re: Using always genereted

[Guillaume Lelarge]

Hi,

2018-07-13 9:29 GMT+02:00 Łukasz Jarych :

> hi,
>
> i am trying to alter table :
>
> ALTER TABLE logging.t_history
>  alter column "id" GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
>
> but this is not working. Error i have.
>
> How to change this properly?
>
>
Which error message do you get?


-- 
Guillaume.

Re: How to watch for schema changes

[Guillaume Lelarge]

2018-07-12 6:12 GMT+02:00 Adrian Klaver :

> On 07/11/2018 08:46 PM, Igor Korot wrote:
>
>> Hi, guys,
>>
>>
>> On Mon, Jul 9, 2018 at 5:38 PM, Adrian Klaver 
>> wrote:
>>
>>> On 07/09/2018 01:49 PM, Igor Korot wrote:
>>>

 Hi, Adrian

 On Tue, Jul 3, 2018 at 1:24 PM, Adrian Klaver <
 adrian.kla...@aklaver.com>
 wrote:

>
> On 07/03/2018 11:15 AM, Igor Korot wrote:
>
>>
>>
>> Adrian,
>>
>> On Tue, Jul 3, 2018 at 12:32 PM, Adrian Klaver
>>  wrote:
>>
>>>
>>>
>>> On 07/03/2018 10:21 AM, Igor Korot wrote:
>>>



 Hi, ALL,
 Is there any trigger or some other means I can do on the server
 which will watch for CREATE/ALTER/DROP TABLE command and after
 successful
 execution of those will issue a NOTIFY statement?

>>>
>>>
>>>
>>>
>>> https://www.postgresql.org/docs/10/static/event-triggers.html
>>>
>>
>>
>>
>> According to the documentation the lowest version it supports is 9.3.
>> Anything prior to that?
>>
>> I'm working with OX 10.8 and it has 9.1 installed.
>>
>
>
> 9.1 went EOL almost two years ago. The oldest supported version is 9.3,
> though it will go EOL this September:
>
> https://www.postgresql.org/support/versioning/
>
> Are you forced to work with 9.1 or can you use something from here:
>
> https://www.postgresql.org/download/macosx/
>
> to get a newer version? FYI that will be a major upgrade so will
> require
> a
> dump/restore or use of pg_upgrade.
>


 Just a thought...
 Is it possible to create a trigger for a system table? Or this
 operation is restricted

>>>
>>>
>>> Easy enough to test. As postgres super user:
>>>
>>> test_(postgres)# create trigger info_test before insert on pg_class
>>> execute
>>> procedure ts_update();
>>>
>>> ERROR:  permission denied: "pg_class" is a system catalog
>>>
>>
>> But
>>
>> draft=# CREATE TRIGGER info_test AFTER INSERT ON
>> information_schema.tables EXECUTE PROCEDURE test();
>> ERROR:  function test() does not exist
>>
>> So it looks like this should be possible?
>>
>
> No, see:
>
> https://www.postgresql.org/docs/10/static/sql-createtrigger.html
>
> AFTER trigger on views are STATEMENT level only.
>
> https://www.postgresql.org/docs/10/static/plpgsql-trigger.html
>
> "NEW
>
> Data type RECORD; variable holding the new database row for
> INSERT/UPDATE operations in row-level triggers. This variable is unassigned
> in statement-level triggers and for DELETE operations.
> OLD
>
> Data type RECORD; variable holding the old database row for
> UPDATE/DELETE operations in row-level triggers. This variable is unassigned
> in statement-level triggers and for INSERT operations.
> "
>
> So you won't know what was INSERTed in row.
>
>
Moreover, there is nothing inserted into this view. It's inserted into
pg_class, and an access to information_schema.tables just reads that
catalog.


>
>> Thank you.
>>
>>
>>>
>>> for when the server is actually being set-up?

 Successful "CREATE TABLE..." statement creates a row inside the
 information_schema.tables
 so if I can create a trigger after this record is inserted or deleted
 that should be fine.

 Thank you.


>
>> And a second question - how do I work with it?
>> I presume that function will have to be compiled in its own module
>> (either dll, so or dylib).
>> But then from the libpq interface how do I call it?
>>
>
>
>
> It can use functions written in PL languages. See below:
>
> https://www.postgresql.org/docs/10/static/sql-createeventtrigger.html
>
> for an example written in plpgsql.
>
>
>
>
>
>
>> Thank you.
>>
>>
>>>
 Thank you.


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

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


-- 
Guillaume.

Re: Create event triger

[Guillaume Lelarge]

2018-07-10 11:28 GMT+02:00 Łukasz Jarych :

> Thank you very much  Guillaume.
>
> Do you know maybe any function to do it automatically?
>
>
Nope, but it should be easy to write a shell script or a DO script to do it.

Best,
> Jacek
>
> wt., 10 lip 2018 o 11:25 Guillaume Lelarge 
> napisał(a):
>
>> 2018-07-10 10:56 GMT+02:00 Łukasz Jarych :
>>
>>> No possible?
>>>
>>>
>> Nope, you need to set up the trigger on each table.
>>
>> Jacek
>>>
>>> pon., 9 lip 2018 o 13:03 Łukasz Jarych  napisał(a):
>>>
>>>> Hi,
>>>>
>>>> i have small database and i am tracking changes using trigger:
>>>>
>>>> CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON t_trig
>>>>
>>>> FOR EACH ROW EXECUTE PROCEDURE change_trigger();
>>>>
>>>> It is possible to create general trigger for all tables?
>>>> Like event trigger?
>>>>
>>>> It would be very helpful for me.
>>>> Now i have to set up this trigger on each table.
>>>>
>>>> Best,
>>>> Jacek
>>>>
>>>
>>
>>
>> --
>> Guillaume.
>>
>


-- 
Guillaume.

Re: Create event triger

[Guillaume Lelarge]

2018-07-10 10:56 GMT+02:00 Łukasz Jarych :

> No possible?
>
>
Nope, you need to set up the trigger on each table.

Jacek
>
> pon., 9 lip 2018 o 13:03 Łukasz Jarych  napisał(a):
>
>> Hi,
>>
>> i have small database and i am tracking changes using trigger:
>>
>> CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON t_trig
>>
>> FOR EACH ROW EXECUTE PROCEDURE change_trigger();
>>
>> It is possible to create general trigger for all tables?
>> Like event trigger?
>>
>> It would be very helpful for me.
>> Now i have to set up this trigger on each table.
>>
>> Best,
>> Jacek
>>
>


-- 
Guillaume.

Re: hardcode password in connect string

[Guillaume Lelarge]

2018-04-15 17:16 GMT+02:00 Raymond O'Donnell :

> On 15/04/18 12:35, Ricardo Martin Gomez wrote:
>
>> Hello David,
>> I think so, the parameter you need in psql command is -w mipassword.
>>
>
> Sorry, this is not correct. -w causes psql *never* to prompt for a
> password, and -W forces a password prompt. Neither allows the password to
> be passed as an argument - I don't think that this is possible with psql.
>
>
You're right, that's not possible (as an argument). And it is a security
*feature*.


> Details here:
>
>   https://www.postgresql.org/docs/10/static/app-psql.html
>
> Ray.
>


-- 
Guillaume.