Re: [GENERAL] startup process stuck in recovery

[Christophe Pettus]

> On Oct 9, 2017, at 17:30, Tom Lane  wrote:
> 
> What am I missing to reproduce the problem?

Not sure.  The actual client behavior here is a bit cryptic (not our code, 
incompletely logs).  They might be creating a savepoint before each temp table 
creation, without a release, but I would expect that to exhaust something else 
first.

--
-- Christophe Pettus
   x...@thebuild.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] startup process stuck in recovery

[Christophe Pettus]

> On Oct 9, 2017, at 18:21, Peter Geoghegan  wrote:
> What's the hot_standy_feedback setting? How about
> max_standby_archive_delay/max_standby_streaming_delay?

On, 5m, 5m.

--
-- Christophe Pettus
   x...@thebuild.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] startup process stuck in recovery

[Peter Geoghegan]

On Mon, Oct 9, 2017 at 12:08 PM, Christophe Pettus  wrote:
> Suggestions on further diagnosis?

What's the hot_standy_feedback setting? How about
max_standby_archive_delay/max_standby_streaming_delay?


-- 
Peter Geoghegan


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] startup process stuck in recovery

[Tom Lane]

Peter Geoghegan  writes:
> Just a guess, but do you disable autovacuum on your dev machine? (I know I 
> do.)

Nope.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] startup process stuck in recovery

[Peter Geoghegan]

On Mon, Oct 9, 2017 at 5:30 PM, Tom Lane  wrote:
> and did not see any untoward behavior, at least not till I got to enough
> temp tables to overrun the master's shared lock table, and even then it
> cleaned up fine.  At no point was the standby process consuming anywhere
> near as much CPU as the master's backend.
>
> What am I missing to reproduce the problem?

Just a guess, but do you disable autovacuum on your dev machine? (I know I do.)

It's possible that this is relevant:
https://postgr.es/m/CAB-EU3RawZx8-OzMfvswFf6z+Y7GOZf03TZ=bez+pbqx+a4...@mail.gmail.com

-- 
Peter Geoghegan


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] startup process stuck in recovery

[Tom Lane]

Christophe Pettus  writes:
> The problem indeed appear to be a very large number of subtransactions, each 
> one creating a temp table, inside a single transaction.  It's made worse by 
> one of those transactions finally getting replayed on the secondary, only to 
> have another one come in right behind it...

Hmm, I tried to reproduce this and could not.  I experimented with
various permutations of this:

create or replace function goslow(n int) returns void language plpgsql as
$$
begin
for i in 1..n loop
  begin
execute 'create temp table myfoo' || i || '(f1 int)';
perform i/0;
  exception when division_by_zero then null;
  end;
end loop;
perform n/0;
end;
$$;

and did not see any untoward behavior, at least not till I got to enough
temp tables to overrun the master's shared lock table, and even then it
cleaned up fine.  At no point was the standby process consuming anywhere
near as much CPU as the master's backend.

What am I missing to reproduce the problem?

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Equivalence Classes when using IN

[David Rowley]

On 10 October 2017 at 12:44, Tom Lane  wrote:
> David Rowley  writes:
>> If the only reason that is_simple_subquery() rejects subqueries with
>> ORDER BY is due to wanting to keep the order by of a view, then
>> couldn't we make is_simple_subquery() a bit smarter and have it check
>> if the subquery is going to be joined to something else, which likely
>> would destroy the order, or at least it would remove any guarantees of
>> it.
>
> I'm not on board with this.  The assumption is that if the user put an
> ORDER BY there, that means they want that subquery to be computed in that
> order.  It's not for us to decide they didn't mean what they said.
>
> Moreover, there are cases where the ORDER BY would be semantically
> significant, eg if there's a LIMIT or volatile functions or tSRFs
> involved.

Ok, thanks for looking, although, FWIW, LIMIT and tSRFs are still disabled.


-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Equivalence Classes when using IN

[Tom Lane]

David Rowley  writes:
> If the only reason that is_simple_subquery() rejects subqueries with
> ORDER BY is due to wanting to keep the order by of a view, then
> couldn't we make is_simple_subquery() a bit smarter and have it check
> if the subquery is going to be joined to something else, which likely
> would destroy the order, or at least it would remove any guarantees of
> it.

I'm not on board with this.  The assumption is that if the user put an
ORDER BY there, that means they want that subquery to be computed in that
order.  It's not for us to decide they didn't mean what they said.

Moreover, there are cases where the ORDER BY would be semantically
significant, eg if there's a LIMIT or volatile functions or tSRFs
involved.

BTW, I noticed that I was wrong upthread about ORDER BY in subqueries
being disallowed by spec --- that was true originally, but they allow
it as of SQL:2008 or thereabouts.  It might be interesting to see if
the spec says anything concrete about the semantics of that.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] startup process stuck in recovery

[Merlin Moncure]

On Mon, Oct 9, 2017 at 6:12 PM, Christophe Pettus  wrote:
>
>> On Oct 9, 2017, at 14:29, Tom Lane  wrote:
>> Hmm.  Creating or dropping a temp table does take AccessExclusiveLock,
>> just as it does for a non-temp table.  In principle we'd not have to
>> transmit those locks to standbys, but I doubt that the WAL code has
>> enough knowledge to filter them out.  So a lot of temp tables and
>> a lot of separate subtransactions could be a nasty combination.
>
> The problem indeed appear to be a very large number of subtransactions, each 
> one creating a temp table, inside a single transaction.  It's made worse by 
> one of those transactions finally getting replayed on the secondary, only to 
> have another one come in right behind it...

FYI, this is pathological coding; it can cause other nastiness such as
exhausting shared memory.  Consider refactoring the code to using a
permanent table (if access is concurrent, you can do tricks like using
'pid' in the key to subdivide the data).

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] startup process stuck in recovery

[Christophe Pettus]

> On Oct 9, 2017, at 14:29, Tom Lane  wrote:
> Hmm.  Creating or dropping a temp table does take AccessExclusiveLock,
> just as it does for a non-temp table.  In principle we'd not have to
> transmit those locks to standbys, but I doubt that the WAL code has
> enough knowledge to filter them out.  So a lot of temp tables and
> a lot of separate subtransactions could be a nasty combination.

The problem indeed appear to be a very large number of subtransactions, each 
one creating a temp table, inside a single transaction.  It's made worse by one 
of those transactions finally getting replayed on the secondary, only to have 
another one come in right behind it...
--
-- Christophe Pettus
   x...@thebuild.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Equivalence Classes when using IN

[David Rowley]

On 10 October 2017 at 02:51, Tom Lane  wrote:
> David Rowley  writes:
>> It's pretty bad practice to have ORDER BY in views. I kinda wish we
>> didn't even allow it, but that ship sailed many years ago...
>
> I think it's actually disallowed by the SQL spec (although so are
> many other things we support).  IMO it's a useful facility to have
> for views that are meant for direct presentation to clients ---
> but if you'd like joins to the view to be optimized, you don't
> want an ORDER BY in there.

If the only reason that is_simple_subquery() rejects subqueries with
ORDER BY is due to wanting to keep the order by of a view, then
couldn't we make is_simple_subquery() a bit smarter and have it check
if the subquery is going to be joined to something else, which likely
would destroy the order, or at least it would remove any guarantees of
it.

Something like the attached?

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


pullup_subqueries_with_order_by_when_its_not_the_only_fromitem.patch
Description: Binary data

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] startup process stuck in recovery

[Tom Lane]

Christophe Pettus  writes:
>> On Oct 9, 2017, at 13:26, Tom Lane  wrote:
>> My bet is that the source server did something that's provoking O(N^2)
>> behavior in the standby server's lock management.  It's hard to say
>> exactly what, but I'm wondering about something like a plpgsql function
>> taking an AccessExclusiveLock inside a loop that repeatedly traps an
>> exception.  Can you correlate where the standby is stuck with what
>> was happening on the source?

> Interestingly, the OIDs for the relations on which the locks on the secondary 
> are held aren't present in pg_class, and they're clustered together.  Could a 
> large number of temporary table creations that are being undone by an abort 
> cause this?

Hmm.  Creating or dropping a temp table does take AccessExclusiveLock,
just as it does for a non-temp table.  In principle we'd not have to
transmit those locks to standbys, but I doubt that the WAL code has
enough knowledge to filter them out.  So a lot of temp tables and
a lot of separate subtransactions could be a nasty combination.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] startup process stuck in recovery

[Christophe Pettus]

> On Oct 9, 2017, at 13:26, Tom Lane  wrote:
> My bet is that the source server did something that's provoking O(N^2)
> behavior in the standby server's lock management.  It's hard to say
> exactly what, but I'm wondering about something like a plpgsql function
> taking an AccessExclusiveLock inside a loop that repeatedly traps an
> exception.  Can you correlate where the standby is stuck with what
> was happening on the source?

Interestingly, the OIDs for the relations on which the locks on the secondary 
are held aren't present in pg_class, and they're clustered together.  Could a 
large number of temporary table creations that are being undone by an abort 
cause this?

--
-- Christophe Pettus
   x...@thebuild.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] startup process stuck in recovery

[Christophe Pettus]

> On Oct 9, 2017, at 13:26, Tom Lane  wrote:
> 
> Oh, that's really interesting.  So it's not *just* releasing locks but
> also acquiring them, which says that it is making progress of some sort.

It seems to have leveled out now, and is still grinding away.

> Can you correlate where the standby is stuck with what
> was happening on the source?

There are definitely some mysterious functions being called, and I'll dig into 
those.

--
-- Christophe Pettus
   x...@thebuild.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] startup process stuck in recovery

[Tom Lane]

Christophe Pettus  writes:
>> On Oct 9, 2017, at 13:01, Tom Lane  wrote:
>> Is that number changing at all?

> Increasing:
>  AccessExclusiveLock |  8810

Oh, that's really interesting.  So it's not *just* releasing locks but
also acquiring them, which says that it is making progress of some sort.

My bet is that the source server did something that's provoking O(N^2)
behavior in the standby server's lock management.  It's hard to say
exactly what, but I'm wondering about something like a plpgsql function
taking an AccessExclusiveLock inside a loop that repeatedly traps an
exception.  Can you correlate where the standby is stuck with what
was happening on the source?

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] startup process stuck in recovery

[Christophe Pettus]

> On Oct 9, 2017, at 13:01, Tom Lane  wrote:
> Hmm.  Is it possible that the process is replaying the abort of a
> transaction with a lot of subtransactions? 

That's possible, although we're now talking about an hours-long delay at this 
point.

> Is that number changing at all?

Increasing:

# select mode, count(*) from pg_locks where pid=5882 group by mode;
mode | count 
-+---
 ExclusiveLock   | 1
 AccessExclusiveLock |  8810
(2 rows)

--
-- Christophe Pettus
   x...@thebuild.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] startup process stuck in recovery

[Tom Lane]

Christophe Pettus  writes:
> The other observation is that the startup process is holding a *lot* of locks:

Hmm.  Is it possible that the process is replaying the abort of a
transaction with a lot of subtransactions?  It seems like maybe
you could be getting into an O(N^2) situation, though for N in
the low thousands it's hard to see that loop taking so long that
you'd think things were stuck.

> # select mode, count(*) from pg_locks where pid=5882 group by mode;
>  AccessExclusiveLock |  7133

Is that number changing at all?

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Question about memory usage of pg_dump

[Condor]

Hello,

I have a question .. okay I know it's a dump but need to ask it because 
probably I will need to tell of five of my collective bad things... :)


My version is PostgreSQL 9.6.5 on x86_64-slackware-linux-gnu, compiled 
by x86_64-slackware-linux-gcc (GCC) 7.2.0, 64-bit on server IP 10.1.1.3 
(the ip of the server is connected with question)
I do a simple backup for few small tables (9M rows each) with bash shell 
script:


for table in table1 table2 table3
do
pg_dump -U postgres --no-tablespaces --no-owner -a -b -t $table 
my_db -h 10.1.1.2 | psql -U data -h 10.1.1.1 my_local_db

done

and I see after done of each table how many rows is copied. All of them, 
but my collective trying to convince me,
they use this way to backup few tables on other project and because 
these tables contain much data (20 - 30M rows as they explain)
pg_dump took too much memory and process was fail because out of memory, 
so they rewrite every things on php.


I think that is a bullshit, they probably smoke something because I 
think with php they add one more level over the whole process because 
they do:


system command to dump the table like mine in shell and send data to 
backup server

read the output
explode output to array
reach end of array with foreach that contain how many rows are copied.

but with my shell script I do only first line:
system command to dump the table like mine in shell and send data to 
backup server


After a short introduction my question is:

How much memory take pg_dump and from witch sever ? 10.1.1.3 and / or 
10.1.1.2. Lets say our data is 100 MB.
I know the dump process lock the table on the server when is read and 
this take memory on server, after that : here I need explanation what is 
happened with few words like: server 10.1.1.2 take 100MB into memory 
then start send rows to server 10.1.1.2 that hold them all 100MB or 
start resend data that receive immediately after receive it to 10.1.1.1  
:



Thanks and sorry of dumb question, but I really need to know what is 
happened on this process to can I tell them: My solution is okay and 
work like a charm.



Cheers,
Hristo S.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] startup process stuck in recovery

[Christophe Pettus]

On Oct 9, 2017, at 12:18, Christophe Pettus  wrote:
> 
> #0  0x558812f4f1da in ?? ()
> #1  0x558812f4f8cb in StandbyReleaseLockTree ()
> #2  0x558812d718ee in ?? ()
> #3  0x558812d75520 in xact_redo ()
> #4  0x558812d7f713 in StartupXLOG ()
> #5  0x558812f0e262 in StartupProcessMain ()
> #6  0x558812d8d4ea in AuxiliaryProcessMain ()
> #7  0x558812f0b2e9 in ?? ()
> #8  0x558812f0dae7 in PostmasterMain ()
> #9  0x558812d0c402 in main ()

The other observation is that the startup process is holding a *lot* of locks:

(pid 5882 is the startup process)

# select mode, count(*) from pg_locks where pid=5882 group by mode;
mode | count 
-+---
 ExclusiveLock   | 1
 AccessExclusiveLock |  7133
(2 rows)

--
-- Christophe Pettus
   x...@thebuild.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] startup process stuck in recovery

[Christophe Pettus]

> On Oct 9, 2017, at 12:10, Tom Lane  wrote:
> 
> Attach to startup process with gdb, and get a stack trace?

#0  0x558812f4f1da in ?? ()
#1  0x558812f4f8cb in StandbyReleaseLockTree ()
#2  0x558812d718ee in ?? ()
#3  0x558812d75520 in xact_redo ()
#4  0x558812d7f713 in StartupXLOG ()
#5  0x558812f0e262 in StartupProcessMain ()
#6  0x558812d8d4ea in AuxiliaryProcessMain ()
#7  0x558812f0b2e9 in ?? ()
#8  0x558812f0dae7 in PostmasterMain ()
#9  0x558812d0c402 in main ()


--
-- Christophe Pettus
   x...@thebuild.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] startup process stuck in recovery

[Tom Lane]

Christophe Pettus  writes:
> We're dealing with a 9.5.5 database with the symptom that, after a certain 
> amount of time after restart, the startup process reaches a certain WAL 
> segment, and stops.  The startup process runs at 100% CPU, with no output 
> from strace.  There are no queries running on the secondary, so it's not a 
> delay for queries.  The WAL receiver continues to operate normally, and we 
> can see sent_location / write_location / flush_location continue to move 
> ahead in parallel, with replay_location stuck in that WAL segment.

> Suggestions on further diagnosis?

Attach to startup process with gdb, and get a stack trace?

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] startup process stuck in recovery

[Christophe Pettus]

Hi,

We're dealing with a 9.5.5 database with the symptom that, after a certain 
amount of time after restart, the startup process reaches a certain WAL 
segment, and stops.  The startup process runs at 100% CPU, with no output from 
strace.  There are no queries running on the secondary, so it's not a delay for 
queries.  The WAL receiver continues to operate normally, and we can see 
sent_location / write_location / flush_location continue to move ahead in 
parallel, with replay_location stuck in that WAL segment.

Suggestions on further diagnosis?

--
-- Christophe Pettus
   x...@thebuild.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Permissions for Web App

[Igal @ Lucee.org]

On 10/9/2017 10:51 AM, David G. Johnston wrote:
On Mon, Oct 9, 2017 at 9:44 AM, Igal @ Lucee.org >wrote:


But I want to give that role permissions on future tables since I
add new tables and drop/recreate current ones.


​ALTER DEFAULT PRIVILEGES​

​https://www.postgresql.org/docs/9.6/static/sql-alterdefaultprivileges.html


It worked, thanks!

For future reference and for the benefit of others, the command that I 
ran is:


  ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO webapp;


Igal Sapir
Lucee Core Developer
Lucee.org 

Re: [GENERAL] Using cp to back up a database?

[Ron Johnson]

On 10/09/2017 01:02 PM, Scott Mead wrote:



On Mon, Oct 9, 2017 at 1:19 PM, Ron Johnson > wrote:


Maybe my original question wasn't clear, so I'll try again: is it safe
to do a physical using cp (as opposed to rsync)?


Yes -- however*you must configure WAL archiving* first.  If not, no backup 
tool, cp, rsync, etc... will provide a good backup.


Oh, and BTW -- The obligatory: You are on an ancient, EOL version of PG.  
Upgrade.


Make sure that these are set:
- archive_mode
- archive_command


Then, on when you restore the backup, you need to create a recovery.conf 
and configure

- restore_command

https://www.postgresql.org/docs/8.4/static/continuous-archiving.html


This is good to know.  Thanks.

--
World Peace Through Nuclear Pacification

Re: [GENERAL] Using cp to back up a database?

[Scott Mead]

On Mon, Oct 9, 2017 at 1:19 PM, Ron Johnson  wrote:

> Maybe my original question wasn't clear, so I'll try again: is it safe to
> do a physical using cp (as opposed to rsync)?
>

Yes -- however* you must configure WAL archiving* first.  If not, no backup
tool, cp, rsync, etc... will provide a good backup.

Oh, and BTW -- The obligatory: You are on an ancient, EOL version of PG.
Upgrade.

Make sure that these are set:
- archive_mode
- archive_command


Then, on when you restore the backup, you need to create a recovery.conf
and configure
- restore_command

https://www.postgresql.org/docs/8.4/static/continuous-archiving.html






>
>
>
> On 10/09/2017 11:49 AM, Darren Douglas wrote:
>
> Ron:
>
> Here is an explanation that may help a bit.
>
> Your script is executing a PHYSICAL backup. A physical backup is simply a
> full copy of the cluster (instance) data directory ($PGDATA). A physical
> backup is your best option when you need to backup the cluster data as well
> as all configuration for the cluster. Essentially, if you had to rebuild
> the entire computer hosting the cluster, you could just reinstall the same
> version of postgres, copy in the backup data directory, and the cluster
> would run exactly as it did before with the same data. A physical backup is
> also necessary when the databases get very large.
>
> In the backup script you posted, the 'pg_start_backup' and
> 'pg_stop_backup' commands fulfill two purposes. The first is to create a
> label for the point in time the backup was started - this is done by
> pg_start_backup. The second is to ensure that all WAL segments that have
> been written since the backup began have been safely archived. That is done
> by pg_stop_backup. This approach is necessary to accomplish an online
> physical backup.
>
> As others have mentioned pg_dump is a LOGICAL backup tool similar to any
> SQL dump you've done with another DBMS. The pg_dump command will do a SQL
> dump to recreate everything within a single database. So, if you have
> multiple databases in your cluster, its not the best option. pg_dumpall is
> the logical backup tool that will do a logical dump of all globals (schema
> + roles) along with all databases in the cluster. Because the
> pg_dump/pg_dumpall commands are not executing a physical backup, the
> pg_start_backup and pg_stop_backup commands do not apply.
>
> As for times when you would elect to do a logical backup, as others have
> mentioned, this is the only valid option when you are restoring to a
> different version of Postgres. It is also a good option to do a backup of a
> single small database or several small databases. And, if for any reason
> the backup needs to be human-readable, this is the approach of choice as
> well.
>
> Darren
>
>
>
> The first
>
> On Mon, Oct 9, 2017 at 6:40 AM, Ron Johnson  wrote:
>
>> Hi,
>>
>> v8.4.20
>>
>> This is what the current backup script uses:
>>
>> /usr/bin/psql -U postgres -c "SELECT pg_start_backup('Incrementalba
>> ckup',true);"
>> cp -r /var/lib/pgsql/data/* $dumpdir/data/
>> /usr/bin/psql -U postgres template1 -c "SELECT pg_stop_backup();"
>>
>>
>> Should it use rsync or pg_dump instead?
>>
>> Thanks
>>
>> --
>> World Peace Through Nuclear Pacification
>>
>>
>
>
> --
> Darren Douglas
> Synse Solutions
> dar...@synsesolutions.com
> 520-661-5885 <(520)%20661-5885>
>
>
>
> --
> World Peace Through Nuclear Pacification
>
>


-- 
--
Scott Mead
Sr. Architect
*OpenSCG *
http://openscg.com

Re: [GENERAL] Permissions for Web App

[David G. Johnston]

On Mon, Oct 9, 2017 at 9:44 AM, Igal @ Lucee.org  wrote:

> But I want to give that role permissions on future tables since I add new
> tables and drop/recreate current ones.
>

​ALTER DEFAULT PRIVILEGES​

​https://www.postgresql.org/docs/9.6/static/sql-alterdefaultprivileges.html

David J.
​

Re: [GENERAL] pg_start/stop_backup naming conventions

[David G. Johnston]

On Mon, Oct 9, 2017 at 9:33 AM, mj0nes  wrote:

> Hi,
>
> I'm just starting out on a rolling backup strategy and the naming
> convention
> has thrown me slightly for the WAL and "backup_label" files.
>
 ​[...]​

> Thanks for any pointers.
>

​I'll give out the standard advice - don't roll-your-own backup solution -
investigate and choose one the many supported and maintained backup
solutions (most are open source) that are already available.  This kind of
detail, then, should become unimportant to you.

David J.

Re: [GENERAL] Using cp to back up a database?

[Ron Johnson]

Maybe my original question wasn't clear, so I'll try again: is it safe to do 
a physical using cp (as opposed to rsync)?



On 10/09/2017 11:49 AM, Darren Douglas wrote:

Ron:

Here is an explanation that may help a bit.

Your script is executing a PHYSICAL backup. A physical backup is simply a 
full copy of the cluster (instance) data directory ($PGDATA). A physical 
backup is your best option when you need to backup the cluster data as 
well as all configuration for the cluster. Essentially, if you had to 
rebuild the entire computer hosting the cluster, you could just reinstall 
the same version of postgres, copy in the backup data directory, and the 
cluster would run exactly as it did before with the same data. A physical 
backup is also necessary when the databases get very large.


In the backup script you posted, the 'pg_start_backup' and 
'pg_stop_backup' commands fulfill two purposes. The first is to create a 
label for the point in time the backup was started - this is done by 
pg_start_backup. The second is to ensure that all WAL segments that have 
been written since the backup began have been safely archived. That is 
done by pg_stop_backup. This approach is necessary to accomplish an online 
physical backup.


As others have mentioned pg_dump is a LOGICAL backup tool similar to any 
SQL dump you've done with another DBMS. The pg_dump command will do a SQL 
dump to recreate everything within a single database. So, if you have 
multiple databases in your cluster, its not the best option. pg_dumpall is 
the logical backup tool that will do a logical dump of all globals (schema 
+ roles) along with all databases in the cluster. Because the 
pg_dump/pg_dumpall commands are not executing a physical backup, the 
pg_start_backup and pg_stop_backup commands do not apply.


As for times when you would elect to do a logical backup, as others have 
mentioned, this is the only valid option when you are restoring to a 
different version of Postgres. It is also a good option to do a backup of 
a single small database or several small databases. And, if for any reason 
the backup needs to be human-readable, this is the approach of choice as well.


Darren



The first

On Mon, Oct 9, 2017 at 6:40 AM, Ron Johnson > wrote:


Hi,

v8.4.20

This is what the current backup script uses:

/usr/bin/psql -U postgres -c "SELECT
pg_start_backup('Incrementalbackup',true);"
cp -r /var/lib/pgsql/data/* $dumpdir/data/
/usr/bin/psql -U postgres template1 -c "SELECT pg_stop_backup();"


Should it use rsync or pg_dump instead?

Thanks

-- 
World Peace Through Nuclear Pacification





--
Darren Douglas
Synse Solutions
dar...@synsesolutions.com 
520-661-5885



--
World Peace Through Nuclear Pacification

Re: [GENERAL] Using cp to back up a database?

[Darren Douglas]

Sorry, forgot about your main question about cp ...

So, because the physical backup is a copy of the entire cluster data
directory, ANY valid and safe method for copying that directory is ok.

In most production installations, that means that an enterprise backup tool
may be used to accomplish the copy. It may mean a shell script that does
backup rotation, etc. However, at its simplest, it may just be a cp or
rsync command.

So yes, very simply, the cp -r command issued in the script, along with
ensuring that WALs are safely archived, is a valid backup approach.

Personally, I would use cp -ar instead. The 'a' option invokes cp in
"archive" mode, which will preserve file permissions.

On Mon, Oct 9, 2017 at 9:49 AM, Darren Douglas 
wrote:

> Ron:
>
> Here is an explanation that may help a bit.
>
> Your script is executing a PHYSICAL backup. A physical backup is simply a
> full copy of the cluster (instance) data directory ($PGDATA). A physical
> backup is your best option when you need to backup the cluster data as well
> as all configuration for the cluster. Essentially, if you had to rebuild
> the entire computer hosting the cluster, you could just reinstall the same
> version of postgres, copy in the backup data directory, and the cluster
> would run exactly as it did before with the same data. A physical backup is
> also necessary when the databases get very large.
>
> In the backup script you posted, the 'pg_start_backup' and
> 'pg_stop_backup' commands fulfill two purposes. The first is to create a
> label for the point in time the backup was started - this is done by
> pg_start_backup. The second is to ensure that all WAL segments that have
> been written since the backup began have been safely archived. That is done
> by pg_stop_backup. This approach is necessary to accomplish an online
> physical backup.
>
> As others have mentioned pg_dump is a LOGICAL backup tool similar to any
> SQL dump you've done with another DBMS. The pg_dump command will do a SQL
> dump to recreate everything within a single database. So, if you have
> multiple databases in your cluster, its not the best option. pg_dumpall is
> the logical backup tool that will do a logical dump of all globals (schema
> + roles) along with all databases in the cluster. Because the
> pg_dump/pg_dumpall commands are not executing a physical backup, the
> pg_start_backup and pg_stop_backup commands do not apply.
>
> As for times when you would elect to do a logical backup, as others have
> mentioned, this is the only valid option when you are restoring to a
> different version of Postgres. It is also a good option to do a backup of a
> single small database or several small databases. And, if for any reason
> the backup needs to be human-readable, this is the approach of choice as
> well.
>
> Darren
>
>
>
> The first
>
> On Mon, Oct 9, 2017 at 6:40 AM, Ron Johnson  wrote:
>
>> Hi,
>>
>> v8.4.20
>>
>> This is what the current backup script uses:
>>
>> /usr/bin/psql -U postgres -c "SELECT pg_start_backup('Incrementalba
>> ckup',true);"
>> cp -r /var/lib/pgsql/data/* $dumpdir/data/
>> /usr/bin/psql -U postgres template1 -c "SELECT pg_stop_backup();"
>>
>>
>> Should it use rsync or pg_dump instead?
>>
>> Thanks
>>
>> --
>> World Peace Through Nuclear Pacification
>>
>>
>
>
> --
> Darren Douglas
> Synse Solutions
> dar...@synsesolutions.com
> 520-661-5885 <(520)%20661-5885>
>



-- 
Darren Douglas
Synse Solutions
dar...@synsesolutions.com
520-661-5885

Re: [GENERAL] Using cp to back up a database?

[Jeff Janes]

On Mon, Oct 9, 2017 at 9:41 AM, Ron Johnson  wrote:

> On 10/09/2017 11:33 AM, Jeff Janes wrote:
>
> On Mon, Oct 9, 2017 at 6:40 AM, Ron Johnson  wrote:
>
>> Hi,
>>
>> v8.4.20
>>
>> This is what the current backup script uses:
>>
>> /usr/bin/psql -U postgres -c "SELECT pg_start_backup('Incrementalba
>> ckup',true);"
>> cp -r /var/lib/pgsql/data/* $dumpdir/data/
>> /usr/bin/psql -U postgres template1 -c "SELECT pg_stop_backup();"
>>
>>
> That's fine, as long as you have a wal archive.  Although I don't know
> what is "Incremental" about it.
>
>
> From reading the docs, that field is just a label with no intrinsic
> meaning.
>

Its purpose is to communicate with you.  If the person who implemented my
backup solution is attempting to communicate with me, and I have no idea
what that communication means, then that makes me quite nervous.

Cheers,

Jeff

Re: [GENERAL] Using cp to back up a database?

[Jeff Janes]

On Mon, Oct 9, 2017 at 7:15 AM, Ron Johnson  wrote:

>
> Sure I want a consistent database.  Why doesn't?
>
> But log shipping requires you to rsync/var/lib/pgsql/data  to the remote
> server, and that's consistent, so why wouldn't rsync to a local directory
> also be consistent?
>

But it isn't consistent by itself.  That is why the log shipping is
required (or an equivalent method of keeping the necessary logs around), to
fix up the consistency.

Cheers,

Jeff

Re: [GENERAL] Using cp to back up a database?

[Darren Douglas]

Ron:

Here is an explanation that may help a bit.

Your script is executing a PHYSICAL backup. A physical backup is simply a
full copy of the cluster (instance) data directory ($PGDATA). A physical
backup is your best option when you need to backup the cluster data as well
as all configuration for the cluster. Essentially, if you had to rebuild
the entire computer hosting the cluster, you could just reinstall the same
version of postgres, copy in the backup data directory, and the cluster
would run exactly as it did before with the same data. A physical backup is
also necessary when the databases get very large.

In the backup script you posted, the 'pg_start_backup' and 'pg_stop_backup'
commands fulfill two purposes. The first is to create a label for the point
in time the backup was started - this is done by pg_start_backup. The
second is to ensure that all WAL segments that have been written since the
backup began have been safely archived. That is done by pg_stop_backup.
This approach is necessary to accomplish an online physical backup.

As others have mentioned pg_dump is a LOGICAL backup tool similar to any
SQL dump you've done with another DBMS. The pg_dump command will do a SQL
dump to recreate everything within a single database. So, if you have
multiple databases in your cluster, its not the best option. pg_dumpall is
the logical backup tool that will do a logical dump of all globals (schema
+ roles) along with all databases in the cluster. Because the
pg_dump/pg_dumpall commands are not executing a physical backup, the
pg_start_backup and pg_stop_backup commands do not apply.

As for times when you would elect to do a logical backup, as others have
mentioned, this is the only valid option when you are restoring to a
different version of Postgres. It is also a good option to do a backup of a
single small database or several small databases. And, if for any reason
the backup needs to be human-readable, this is the approach of choice as
well.

Darren



The first

On Mon, Oct 9, 2017 at 6:40 AM, Ron Johnson  wrote:

> Hi,
>
> v8.4.20
>
> This is what the current backup script uses:
>
> /usr/bin/psql -U postgres -c "SELECT pg_start_backup('
> Incrementalbackup',true);"
> cp -r /var/lib/pgsql/data/* $dumpdir/data/
> /usr/bin/psql -U postgres template1 -c "SELECT pg_stop_backup();"
>
>
> Should it use rsync or pg_dump instead?
>
> Thanks
>
> --
> World Peace Through Nuclear Pacification
>
>


-- 
Darren Douglas
Synse Solutions
dar...@synsesolutions.com
520-661-5885

[GENERAL] Permissions for Web App

[Igal @ Lucee.org]

Hello,

I created a role named `webapp` as follows:

  CREATE ROLE webapp WITH LOGIN PASSWORD 'changeme';

While in development, I want to give that role permissions on all tables 
in schema public.  So far I've been using the following command, which 
works on existing tables:


  GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO webapp;

But I want to give that role permissions on future tables since I add 
new tables and drop/recreate current ones.


How can I do that?

Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org 

Re: [GENERAL] Using cp to back up a database?

[Ron Johnson]

On 10/09/2017 11:33 AM, Jeff Janes wrote:
On Mon, Oct 9, 2017 at 6:40 AM, Ron Johnson > wrote:


Hi,

v8.4.20

This is what the current backup script uses:

/usr/bin/psql -U postgres -c "SELECT
pg_start_backup('Incrementalbackup',true);"
cp -r /var/lib/pgsql/data/* $dumpdir/data/
/usr/bin/psql -U postgres template1 -c "SELECT pg_stop_backup();"


That's fine, as long as you have a wal archive. Although I don't know what 
is "Incremental" about it.


From reading the docs, that field is just a label with no intrinsic meaning.


--
World Peace Through Nuclear Pacification

[GENERAL] pg_start/stop_backup naming conventions

[mj0nes]

Hi,

I'm just starting out on a rolling backup strategy and the naming convention
has thrown me slightly for the WAL and "backup_label" files.

What I want to do is pair up the backup label files with the associated tar
ball of the data directory. When I first ran pg_start_backup('label'), I
naively thought a file would be created named label. Maybe not that naive
considering the documentation:

/pg_start_backup creates a backup label file, *called backup_label*, in the
cluster directory with information about your backup, including the start
time and label string./

I've since of course learnt that the file created is in some way related to
the WAL files referenced in the return from pg_start_backup - but it's not
really a transparent mapping from e.g. 1/F328 to
0001000100F3.0028.backup. I appreciate the are telltale
similarities between the two, but to be sure of matching programmatically
I'd really rather know the naming convention. Why are there two fewer zeros
preceding the 28 in the value returned from pg_start_backup? What's the 28?
Why the "1/"? Can this pattern always be relied upon?

It might be easier if the backup file actually appeared locally, as the
documentation suggests, that way I could name the tar file accordingly. But
it doesn't, it gets shipped off to the remote server in the same way as WAL
files via the archive setup.

The documentation could be a bit more explicit around this.

Thanks for any pointers.



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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using cp to back up a database?

[Jeff Janes]

On Mon, Oct 9, 2017 at 6:40 AM, Ron Johnson  wrote:

> Hi,
>
> v8.4.20
>
> This is what the current backup script uses:
>
> /usr/bin/psql -U postgres -c "SELECT pg_start_backup('
> Incrementalbackup',true);"
> cp -r /var/lib/pgsql/data/* $dumpdir/data/
> /usr/bin/psql -U postgres template1 -c "SELECT pg_stop_backup();"
>
>
That's fine, as long as you have a wal archive.  Although I don't know what
is "Incremental" about it.  If you upgrade to a version which wasn't quite
so ancient, you could use pg_basebackup.


>
> Should it use rsync or pg_dump instead?
>

rsync is dangerous if not used with great care, and probably isn't going to
get you much for just doing a backup.

pg_dump is also fine, but it does something different, it creates a logical
backup, not a physical backup.  The backup from pg_dump cannot be used to
seed a PITR or streaming replica.  On the other hand, it can restored into
a database from a different version and different architecture.   And with
pg_dump the pg_start_backup and pg_stop_backup are useless and confusing.

Cheers

Re: [GENERAL] Using cp to back up a database?

[Achilleas Mantzios]

On 09/10/2017 17:13, Michael Paquier wrote:

On Mon, Oct 9, 2017 at 11:09 PM, Achilleas Mantzios
 wrote:

In all pg conferences I have been, ppl scream : do not use pg_dump for
backups :)

It depends on what you are trying to achieve, pg_dump can be fine for
small-ish databases. By relying on both logical (pg_dump) and physical
backups (base backups) brings more insurance in face of a disaster.


Basically yes, it depends on size. Where I work in order to do a proper pg_dump 
would take ~ one week. Business can't wait for that long.


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



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using cp to back up a database?

[Ron Johnson]

Sure I want a consistent database.  Why doesn't?

But log shipping requires you to rsync/var/lib/pgsql/data to the remote 
server, and that's consistent, so why wouldn't rsync to a local directory 
also be consistent?


On 10/09/2017 08:51 AM, Larry Rosenman wrote:


If you want a consistent database (you **REALLY** do), pg_dump is the 
correct tool.


--

Larry Rosenman http://www.lerctr.org/~ler 

Phone: +1 214-642-9640 E-Mail: l...@lerctr.org 



US Mail: 5708 Sabbia Drive, Round Rock, TX 78665-2106

*From: * on behalf of Ron Johnson 


*Date: *Monday, October 9, 2017 at 8:41 AM
*To: *"pgsql-general@postgresql.org" 
*Subject: *[GENERAL] Using cp to back up a database?

Hi,

v8.4.20

This is what the current backup script uses:

/usr/bin/psql -U postgres -c "SELECT 
pg_start_backup('Incrementalbackup',true);"


cp -r /var/lib/pgsql/data/* $dumpdir/data/

/usr/bin/psql -U postgres template1 -c "SELECT pg_stop_backup();"



Should it use rsync or pg_dump instead?

Thanks




--
World Peace Through Nuclear Pacification

Re: [GENERAL] Using cp to back up a database?

[Michael Paquier]

On Mon, Oct 9, 2017 at 11:09 PM, Achilleas Mantzios
 wrote:
> On 09/10/2017 16:51, Larry Rosenman wrote:
>
> If you want a consistent database (you *REALLY* do), pg_dump is the correct
> tool.
>
> In all pg conferences I have been, ppl scream : do not use pg_dump for
> backups :)

It depends on what you are trying to achieve, pg_dump can be fine for
small-ish databases. By relying on both logical (pg_dump) and physical
backups (base backups) brings more insurance in face of a disaster.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using cp to back up a database?

[Achilleas Mantzios]

On 09/10/2017 16:51, Larry Rosenman wrote:


If you want a consistent database (you **REALLY** do), pg_dump is the correct 
tool.



In all pg conferences I have been, ppl scream : do not use pg_dump for backups 
:)


--

Larry Rosenman http://www.lerctr.org/~ler 

Phone: +1 214-642-9640 E-Mail: l...@lerctr.org 


US Mail: 5708 Sabbia Drive, Round Rock, TX 78665-2106

*From: * on behalf of Ron Johnson 

*Date: *Monday, October 9, 2017 at 8:41 AM
*To: *"pgsql-general@postgresql.org" 
*Subject: *[GENERAL] Using cp to back up a database?

Hi,

v8.4.20

This is what the current backup script uses:

/usr/bin/psql -U postgres -c "SELECT pg_start_backup('Incrementalbackup',true);"

cp -r /var/lib/pgsql/data/* $dumpdir/data/

/usr/bin/psql -U postgres template1 -c "SELECT pg_stop_backup();"



Should it use rsync or pg_dump instead?

Thanks


--
World Peace Through Nuclear Pacification



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

Re: [GENERAL] Using cp to back up a database?

[Melvin Davidson]

On Mon, Oct 9, 2017 at 9:51 AM, Larry Rosenman  wrote:

> If you want a consistent database (you **REALLY** do), pg_dump is the
> correct tool.
>
>
>
> --
>
> Larry Rosenman http://www.lerctr.org/~ler
>
> Phone: +1 214-642-9640 <(214)%20642-9640> E-Mail:
> l...@lerctr.org
>
> US Mail: 5708 Sabbia Drive, Round Rock, TX 78665
> 
> -2106
>
>
>
>
>
> *From: * on behalf of Ron Johnson <
> ron.l.john...@cox.net>
> *Date: *Monday, October 9, 2017 at 8:41 AM
> *To: *"pgsql-general@postgresql.org" 
> *Subject: *[GENERAL] Using cp to back up a database?
>
>
>
> Hi,
>
> v8.4.20
>
> This is what the current backup script uses:
>
> /usr/bin/psql -U postgres -c "SELECT pg_start_backup('
> Incrementalbackup',true);"
>
> cp -r /var/lib/pgsql/data/* $dumpdir/data/
>
> /usr/bin/psql -U postgres template1 -c "SELECT pg_stop_backup();"
>
>
>
> Should it use rsync or pg_dump instead?
>
> Thanks
>
>
> --
>
> World Peace Through Nuclear Pacification
>
>
>Should it use rsync or pg_dump instead?

It is a matter of choice. What you are doing with the script is making a
backup of the entire PostgreSQL data file directory. That includes all
PostgreSQL system catalogs as well as user objects. A restore of your data
directory would be faster than a full restore of pg_dump as indexes and
constraints would not have to be recreated. However, it does not give you
the option to restore one or more selected objects.
pg_dump, on the other hand, gives you the flexibility of selecting what to
backup and what to restore. FWIW, I much prefer pg_dump.

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

Re: [GENERAL] Using cp to back up a database?

[Larry Rosenman]

If you want a consistent database (you *REALLY* do), pg_dump is the correct 
tool.

 

-- 

Larry Rosenman http://www.lerctr.org/~ler

Phone: +1 214-642-9640 E-Mail: l...@lerctr.org

US Mail: 5708 Sabbia Drive, Round Rock, TX 78665-2106

 

 

From:  on behalf of Ron Johnson 

Date: Monday, October 9, 2017 at 8:41 AM
To: "pgsql-general@postgresql.org" 
Subject: [GENERAL] Using cp to back up a database?

 

Hi,

v8.4.20

This is what the current backup script uses:

/usr/bin/psql -U postgres -c "SELECT pg_start_backup('Incrementalbackup',true);"

cp -r /var/lib/pgsql/data/* $dumpdir/data/

/usr/bin/psql -U postgres template1 -c "SELECT pg_stop_backup();"



Should it use rsync or pg_dump instead?

Thanks


-- 
World Peace Through Nuclear Pacification

Re: [GENERAL] Equivalence Classes when using IN

[Tom Lane]

David Rowley  writes:
> It's not all that clear what your view is doing here. Confusingly
> there's a Sort in the plan, yet nothing in the query asked for that,
> so I guess that the view must have an ORDER BY. If you get rid of that
> the planner would likely use an index on product (customer_id) to
> parameterise the nested loop, at least, it likely would, if you have
> one.

Yeah.  The ORDER BY creates a partial optimization fence, preventing
any such plan from being considered.

> It's pretty bad practice to have ORDER BY in views. I kinda wish we
> didn't even allow it, but that ship sailed many years ago...

I think it's actually disallowed by the SQL spec (although so are
many other things we support).  IMO it's a useful facility to have
for views that are meant for direct presentation to clients ---
but if you'd like joins to the view to be optimized, you don't
want an ORDER BY in there.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Using cp to back up a database?

[Ron Johnson]

Hi,

v8.4.20

This is what the current backup script uses:

/usr/bin/psql -U postgres -c "SELECT pg_start_backup('Incrementalbackup',true);"
cp -r /var/lib/pgsql/data/* $dumpdir/data/
/usr/bin/psql -U postgres template1 -c "SELECT pg_stop_backup();"


Should it use rsync or pg_dump instead?

Thanks

--
World Peace Through Nuclear Pacification

[GENERAL] Error: "cached plan must not change result type"

[Durumdara]

Dear Members!


At Friday one of our clients got this error:

"cached plan must not change result type"

He restarted the application and the problem vanished.


We used PGDAC to access the database.


Firstly we didn't know nothing about this kind of error.


But later we realized that somebody in our company altered a very often
used table.

He added only one field with default value while they were using the
programme (they didn't exit).


Just for we will be sure: these modifications could generate this kind of
error?

What is the complete description of this error? What we need to not do for
avoid it?


Thank you!


Best Regards

   dd

Re: [GENERAL] Equivalence Classes when using IN

[David Rowley]

On 9 October 2017 at 22:39, Kim Rose Carlsen  wrote:
> EXPLAIN ANALYZE
>  SELECT *
>FROM customer
>JOIN view_customer
>  ON customer.customer_id = view_customer.customer_id
>   WHERE age < 20;
>
>  QUERY PLAN
> -
>  Nested Loop Left Join  (cost=139.00..10392.96 rows=668 width=16) (actual 
> time=0.528..35.120 rows=200 loops=1)
>Join Filter: (c.customer_id = product.customer_id)
>Rows Removed by Join Filter: 199900
>->  Nested Loop  (cost=0.28..199.21 rows=334 width=12) (actual 
> time=0.075..1.146 rows=100 loops=1)
>  ->  Seq Scan on customer  (cost=0.00..21.51 rows=334 width=8) 
> (actual time=0.067..0.282 rows=100 loops=1)
>Filter: (age < 20)
>Rows Removed by Filter: 901
>  ->  Index Only Scan using customer_pkey on customer c  
> (cost=0.28..0.53 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=100)
>Index Cond: (customer_id = customer.customer_id)
>Heap Fetches: 100
>->  Materialize  (cost=138.73..173.75 rows=2001 width=8) (actual 
> time=0.005..0.130 rows=2001 loops=100)
>  ->  Sort  (cost=138.73..143.73 rows=2001 width=8) (actual 
> time=0.448..0.588 rows=2001 loops=1)
>Sort Key: product.customer_id, product.product_id
>Sort Method: quicksort  Memory: 142kB
>->  Seq Scan on product  (cost=0.00..29.01 rows=2001 width=8) 
> (actual time=0.006..0.215 rows=2001 loops=1)
>  Planning time: 0.214 ms
>  Execution time: 35.284 ms

You would benefit from adding the age column to view_customer, or at
least consider having some view which contains all the columns you'll
ever need from those tables and if you need special views with only a
subset of columns due to some software doing "select * from
viewname;", then you could just create some. Joining to the same table
again seems like a bit of a waste of effort for the planner and
executor.  I'd assume customer_id is the PRIMARY KEY of customer and
is unique.

It's not all that clear what your view is doing here. Confusingly
there's a Sort in the plan, yet nothing in the query asked for that,
so I guess that the view must have an ORDER BY. If you get rid of that
the planner would likely use an index on product (customer_id) to
parameterise the nested loop, at least, it likely would, if you have
one.

It's pretty bad practice to have ORDER BY in views. I kinda wish we
didn't even allow it, but that ship sailed many years ago...

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Equivalence Classes when using IN

[Kim Rose Carlsen]

>On 9 October 2017 at 08:01, Kim Rose Carlsen  wrote:


>> Is this because postgres never consider IN clause when building equivalence
>> class's?
>
>Only btree equality operators are considered at the moment.

After good night sleep and reading the previous discussion, I am no longer sure 
I have reduced my original problem to the right example. If we continue from 
previous setup and add the following:

   ALTER TABLE customer ADD COLUMN age INTEGER;
   UPDATE customer SET age = customer_id / 5;

CREATE INDEX ON customer (age);
CREATE INDEX ON product (customer_id);

   SET enable_hashjoin = false;
   SET enable_mergejoin = false;
    
    EXPLAIN ANALYZE
 SELECT * 
   FROM customer 
   JOIN view_customer 
 ON customer.customer_id = view_customer.customer_id 
  WHERE age < 20;

 QUERY PLAN 
 
-
 Nested Loop Left Join  (cost=139.00..10392.96 rows=668 width=16) (actual 
time=0.528..35.120 rows=200 loops=1)
   Join Filter: (c.customer_id = product.customer_id)
   Rows Removed by Join Filter: 199900
   ->  Nested Loop  (cost=0.28..199.21 rows=334 width=12) (actual 
time=0.075..1.146 rows=100 loops=1)
 ->  Seq Scan on customer  (cost=0.00..21.51 rows=334 width=8) (actual 
time=0.067..0.282 rows=100 loops=1)
   Filter: (age < 20)
   Rows Removed by Filter: 901
 ->  Index Only Scan using customer_pkey on customer c  
(cost=0.28..0.53 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=100)
   Index Cond: (customer_id = customer.customer_id)
   Heap Fetches: 100
   ->  Materialize  (cost=138.73..173.75 rows=2001 width=8) (actual 
time=0.005..0.130 rows=2001 loops=100)
 ->  Sort  (cost=138.73..143.73 rows=2001 width=8) (actual 
time=0.448..0.588 rows=2001 loops=1)
   Sort Key: product.customer_id, product.product_id
   Sort Method: quicksort  Memory: 142kB
   ->  Seq Scan on product  (cost=0.00..29.01 rows=2001 width=8) 
(actual time=0.006..0.215 rows=2001 loops=1)
 Planning time: 0.214 ms
 Execution time: 35.284 ms


The planner prefer to use hash and merge joins which is ok, when many rows are 
to be joined, I don't think any condition can be merged to make these case 
faster. I have disabled merge and hash joins to get to a nested loop join 
instead, in this case it would be much better if customer_id can be pulled 
inside the loop, so it can look at only the relevant rows and not all rows for 
each loop. I somehow inferred that this would be the same as selecting from the 
view using IN clause, now I'm not so sure anymore.

I can see there is a trade off between planner time and how exotic the case is. 
If you want to be able to hide abstraction through views I guess the nature 
becomes more OLAP oriented than OLTP. 

Best Regards
Kim Carlsen

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] OR-clause support for indexes

[David Rowley]

On 8 October 2017 at 21:30, Andreas Joseph Krogh  wrote:
> There was a while ago a proposed patch for adding $subject;
> https://commitfest.postgresql.org/8/454/

That looks like it's been abandoned, but perhaps it's worth asking the
author directly?

> Is this being worked on? Any progress in btree-support?

Not exactly what you're asking, but perhaps
https://commitfest.postgresql.org/14/1001/ could improve your
workload, or perhaps you could just manually rewrite the query.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Automatically check for anti-patterns in SQL queries

[Thomas Kellerer]

Joy Arulraj schrieb am 08.10.2017 um 13:50:
> Hi folks -- We developed a static analysis tool, called SQLCheck, for
> automatically identifying anti-patterns in SQL queries.
> 
> https://github.com/jarulraj/sqlcheck
> 
> Our goal is to provide hints to the developers about potential
> performance and security issues present in SQL queries. I believe
> that integrating such a tool (maybe using a special SQL clause) with
> PostgreSQL can benefit users. Feedback would be much appreciated.

Looks interesting. 

However on Windows 10 this does not seem to work. e.g.:

   sqlcheck -h

simply does nothing. The program briefly starts (I can see the title of my 
cmd.exe changed) but then exists immediately without even showing the help. 

Something like "sqlcheck -f test.sql" also shows no result at all (no error 
message, no output, nothing)

Regards
Thomas
 



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general