Re: Issue on public schéma with Pg_restore

[Thomas Poty]

Hi,

*About version :*

This is the same on both server

* + source  server  :*


[[local]] thomasproot@serverconfig=# select version();
┌──┐
│
version  │
├──┤
│ PostgreSQL 9.6.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-16), 64-bit │
└──┘
(1 row)

Time: 0.183 ms

*+ target server*

[[local]] thomasproot@postgres=# select version();
┌──┐
│
version  │
├──┤
│ PostgreSQL 9.6.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-16), 64-bit │
└──┘
(1 row)

Time: 4.711 ms


*+ pg_dump :*

*Command :*
/usr/pgsql-9.6/bin/pg_dump --username=thomasp --host=VM65 --port=5432
--quote-all-identifiers --blobs --format=c --compress=0 --verbose
serverconfig >
/mnt/backupPostgreSQL/serverconfig_prod/backup_in_progress/serverconfig_prod_thomasp.sql

*version :*
pg_dump (PostgreSQL) 9.6.7


*+ pg_restore :*

*command :*
/bin/pg_restore --username=backup --host=VM38 --port=5432 --dbname=postgres
--no-password --disable-triggers --verbose --clean --create --if-exists
/mnt/backupPostgreSQL/serverconfig_prod/backup_in_progress/serverconfig_prod_thomasp.sql

*version :*
pg_restore (PostgreSQL) 9.6.7




*About privileges: *
*+ source db :*

[[local]] thomasproot@serverconfig=# \dn+
   List of schemas
┌──┬┬──┬┐
│   Name│   Owner │
   Access privileges
│  Description│
├──┼┼──┼┤
│ public  │ serverconfig_owner│
serverconfig_owner=UC/serverconfig_owner↵   │
standard public schema   │
│   │  │
toolboxsysadmin=U/serverconfig_owner
│  │
│ public_h  │ serverconfig_owner │
serverconfig_owner=UC/serverconfig_owner↵
│  │
│   │  │
toolboxsysadmin=U/serverconfig_owner
│  │
└──┴┴──┴┘
(2 rows)


*+ target db  *

*schema of template1 :*

thomasproot@template1=# \dn+
 List of schemas
┌──┬───┬───┬─┐
│ Name │ Owner  │ Access privileges│ Description
 │
├──┼───┼───┼─┤
└──┴───┴───┴─┘
(0 rows)


*after restore:*


[[local]] thomasproot@serverconfig=# \dn+
   List of schemas
┌──┬┬──┬┐
│   Name│   Owner │
   Access privileges
│  Description│
├──┼┼──┼┤
│ public  │ serverconfig_owner│
serverconfig_owner=UC/serverconfig_owner   ↵│
standard public schema   │
│   │  │*
=UC/postgres *
↵│
│
│   │  │
toolboxsysadmin=U/serverconfig_owner
│  │
│ public_h  │ serverconfig_owner │
serverconfig_owner=UC/serverconfig_owner↵
│  │
│   │  │
toolboxsysadmin=U/serverconfig_owner
│  │
└──┴┴──┴┘
(2 rows)


It seems pg_restore automtically add privileges usage and create for public
r

Re: Issue on public schéma with Pg_restore

[Thomas Poty]

I have run this command (same of previous one without -d and with -f
argument :

/bin/pg_restore --username=backup --host=VM38 --port=5432  --no-password
--disable-triggers --verbose --clean --create --if-exists -f
/tmp/thomasp.log /mnt/backupPostgreSQL/serverco
nfig_prod/backup_in_progress/serverconfig_prod_thomasp.sql

Here is partial content of the file /tmp/thomasp.log
I don't see any "create schema public"... :

BUT I see  create database ... TEMPLATE=*template0*



*CREATE DATABASE "serverconfig" WITH TEMPLATE = template0 ENCODING = 'UTF8'
LC_COLLATE = 'en_GB.UTF-8' LC_CTYPE = 'en_GB.UTF-8';ALTER DATABASE
serverconfig OWNER TO serverconfig_owner;\connect serverconfig*


At this time for my issue, I see  solutions :
- never use schema public (so rename it)=>
   dropping it after a create database or a pg_restore with --create
- use a postscript to revoke all privileges  from public


According to me, pg_dump/pg_restore could add new features :
- An other solution could be a new argument in order to specify a template
(like --template) only relevent with --create of pg_restore
- An other solution could be :
  with pg_dump : include create statement for all schema
  with --create of pg_restore, have this behavior  : create the
database, drop all in the new db , and create all schema included in the
dump.
- An other solution could be : a new argument in order to specify an other
template than template0 (like --template) only relevent with --create of
pg_restore.

Is it possible to discuss about these potentiel features? with whom? Is
there a specific canal?

i would like to thank Adrian.

Regards Thomas

2018-04-26 9:03 GMT+02:00 Thomas Poty :

> Hi,
>
> *About version :*
>
> This is the same on both server
>
> * + source  server  :*
>
>
> [[local]] thomasproot@serverconfig=# select version();
> ┌───
> ───┐
> │
> version  │
> ├───
> ───┤
> │ PostgreSQL 9.6.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
> 20150623 (Red Hat 4.8.5-16), 64-bit │
> └───
> ───┘
> (1 row)
>
> Time: 0.183 ms
>
> *+ target server*
>
> [[local]] thomasproot@postgres=# select version();
> ┌───
> ───┐
> │
> version  │
> ├───
> ───┤
> │ PostgreSQL 9.6.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
> 20150623 (Red Hat 4.8.5-16), 64-bit │
> └───
> ───┘
> (1 row)
>
> Time: 4.711 ms
>
>
> *+ pg_dump :*
>
> *Command :*
> /usr/pgsql-9.6/bin/pg_dump --username=thomasp --host=VM65 --port=5432
> --quote-all-identifiers --blobs --format=c --compress=0 --verbose
> serverconfig > /mnt/backupPostgreSQL/serverconfig_prod/backup_in_
> progress/serverconfig_prod_thomasp.sql
>
> *version :*
> pg_dump (PostgreSQL) 9.6.7
>
>
> *+ pg_restore :*
>
> *command :*
> /bin/pg_restore --username=backup --host=VM38 --port=5432
> --dbname=postgres --no-password --disable-triggers --verbose --clean
> --create --if-exists /mnt/backupPostgreSQL/serverconfig_prod/backup_in_
> progress/serverconfig_prod_thomasp.sql
>
> *version :*
> pg_restore (PostgreSQL) 9.6.7
>
>
>
>
> *About privileges: *
> *+ source db :*
>
> [[local]] thomasproot@serverconfig=# \dn+
>List of schemas
> ┌──┬┬───
> ───┬┐
> │   Name│   Owner │
>  Access privileges
> │  Description│
> ├──┼┼───
> ───┼┤
> │ public  │ serverconfig_owner│
> serverconfig_owner=UC/serverconfig_owner↵   │
> standard public schema   │
> │   │  │
> toolboxsysadmin=U/serverconfig_owner
> │  │
> │ public_h  │ serverconfig_owner │
> serverconfig_owner=UC/serverconfig_owner↵
> │  │
> │   │  │
> toolboxsysadmin=U/serverconfig_owner
> │  │
> └──┴┴───
> ─

Re: Same condition in the CTE and in the subsequent JOIN using it

[Alexander Farber]

Thank you for the insightful comments.

Actually in my specific case I have managed to get rid of the (almost) same
condition in the outer query:

CREATE OR REPLACE FUNCTION words_stat_scores_2(
in_social integer,
in_sidtext
) RETURNS TABLE (
out_day   text,
out_diff  numeric,
out_score numeric
) AS
$func$
WITH filtered_moves AS (
SELECT
m.uid,
s.uid AS web_script_viewer,
DATE_TRUNC('day', m.played) AS day,
m.mid,
EXTRACT(EPOCH FROM m.played - LAG(m.played) OVER
(PARTITION BY m.gid ORDER BY m.played))::int/60 AS diff
FROMwords_moves m
JOINwords_games g ON (m.gid = g.gid)
JOINwords_social s ON (s.uid IN (g.player1, g.player2))
WHERE   s.social = in_social
AND s.sid = in_sid
AND m.played > CURRENT_TIMESTAMP - interval '2 month'
)
SELECT
TO_CHAR(f.day, 'DD.MM.'),
ROUND(AVG(f.diff)),
ROUND(AVG(m.score), 1)
FROMwords_moves m
JOINfiltered_moves f using(mid)
WHERE   f.uid = f.web_script_viewer  -- INSTEAD OF DOING JOIN
ON words_social AGAIN
AND m.action = 'play'
GROUP BY f.day
ORDER BY f.day;

$func$ LANGUAGE sql STABLE;

The "big picture" of my database is that every player data can be referred
by the numeric "uid" (user id).

But when a user comes though a web script, then he must first authenticate
through words_social table, I can trust him just giving me some "uid".

(I suppose many databases have similar "authentication" table, storing
usernames/passwords)

And thus my question is about how to only authenticate once - and then
carry this result through several CTEs.

Regards
Alex

Re: Same condition in the CTE and in the subsequent JOIN using it

[Alexander Farber]

s/ I can trust / I can't trust /

Re: Issue on public schéma with Pg_restore

[Adrian Klaver]

On 04/26/2018 12:03 AM, Thomas Poty wrote:

Hi,


Comments in line below.



*About version :*

This is the same on both server

_ + source  server  :_


[[local]] thomasproot@serverconfig=# select version();
┌──┐
│ 
version  │

├──┤
│ PostgreSQL 9.6.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 
20150623 (Red Hat 4.8.5-16), 64-bit │

└──┘
(1 row)

Time: 0.183 ms

_+ target server_

[[local]] thomasproot@postgres=# select version();
┌──┐
│ 
version  │

├──┤
│ PostgreSQL 9.6.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 
20150623 (Red Hat 4.8.5-16), 64-bit │

└──┘
(1 row)

Time: 4.711 ms


The latest 9.6 version is 9.6.8 and it has changes to deal with this:

https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path

Not sure if it would cover your issues, but worth the ugrade anyway.








_+ pg_restore :_

/command :/
/bin/pg_restore --username=backup --host=VM38 --port=5432 
--dbname=postgres --no-password --disable-triggers --verbose --clean 
--create --if-exists 
/mnt/backupPostgreSQL/serverconfig_prod/backup_in_progress/serverconfig_prod_thomasp.sql


FYI, --disable-triggers in this context is a no-op.



/version :/
pg_restore (PostgreSQL) 9.6.7


*About privileges:

*
_+ source db :_

[[local]] thomasproot@serverconfig=# \dn+
    List of schemas
┌──┬┬──┬┐
│   Name            │   Owner                         │ 
                                                Access 
privileges │  Description                                │

├──┼┼──┼┤
│ public              │ serverconfig_owner    │ 
serverconfig_owner=UC/serverconfig_owner↵   │ 
standard public schema                   │
│                   │                                  │ 
toolboxsysadmin=U/serverconfig_owner   
│                                      │
│ public_h  │ serverconfig_owner                 │ 
serverconfig_owner=UC/serverconfig_owner↵   
│                                      │
│                   │                              │ 
toolboxsysadmin=U/serverconfig_owner   
│                                      │

└──┴┴──┴┘
(2 rows)



So how did you revoke the privileges for PUBLIC in the above?



_+ target db _

/schema of template1 :/

thomasproot@template1=# \dn+
  List of schemas
┌──┬───┬───┬─┐
│ Name     │ Owner  │ Access privileges    │ Description 
            │

├──┼───┼───┼─┤
└──┴───┴───┴─┘
(0 rows)


/after restore:/*

*
[[local]] thomasproot@serverconfig=# \dn+
    List of schemas
┌──┬┬──┬┐
│ Name            │   Owner                         │   
                                  Access privileges │  
Description │

├──┼┼──┼┤
│ public              │ serverconfig_owner    │ 
serverconfig_owner=UC/serverconfig_owner   ↵│ 
standard public schema                   │
│               │  
│*=UC/postgres * 
     ↵│ 
  │
│ │                                  │ 
toolboxsysadmin=U/serverconfig_owner │   
            │
│ public_h  │ serverconfig_owner                 │ 
serverconfig_owner=UC/serve

Re: Issue on public schéma with Pg_restore

[Thomas Poty]

Hi,

About the minor version, we will plan the upgrade soon .

So how did you revoke the privileges for PUBLIC in the above? [db source]
=> After creation of the database, I executed:
revoke all on schema public from public;

Thanks for the 'no-op' .

Thomas



2018-04-26 16:28 GMT+02:00 Adrian Klaver :

> On 04/26/2018 12:03 AM, Thomas Poty wrote:
>
>> Hi,
>>
>
> Comments in line below.
>
>
>> *About version :*
>>
>> This is the same on both server
>>
>> _ + source  server  :_
>>
>>
>> [[local]] thomasproot@serverconfig=# select version();
>> ┌───
>> ───┐
>> │
>>  version  │
>> ├───
>> ───┤
>> │ PostgreSQL 9.6.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
>> 20150623 (Red Hat 4.8.5-16), 64-bit │
>> └───
>> ───┘
>> (1 row)
>>
>> Time: 0.183 ms
>>
>> _+ target server_
>>
>> [[local]] thomasproot@postgres=# select version();
>> ┌───
>> ───┐
>> │
>>  version  │
>> ├───
>> ───┤
>> │ PostgreSQL 9.6.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
>> 20150623 (Red Hat 4.8.5-16), 64-bit │
>> └───
>> ───┘
>> (1 row)
>>
>> Time: 4.711 ms
>>
>
> The latest 9.6 version is 9.6.8 and it has changes to deal with this:
>
> https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_
> Protect_Your_Search_Path
>
> Not sure if it would cover your issues, but worth the ugrade anyway.
>
>
>>
>>
>
>> _+ pg_restore :_
>>
>> /command :/
>> /bin/pg_restore --username=backup --host=VM38 --port=5432
>> --dbname=postgres --no-password --disable-triggers --verbose --clean
>> --create --if-exists /mnt/backupPostgreSQL/serverco
>> nfig_prod/backup_in_progress/serverconfig_prod_thomasp.sql
>>
>
> FYI, --disable-triggers in this context is a no-op.
>
>
>> /version :/
>> pg_restore (PostgreSQL) 9.6.7
>>
>>
>> *About privileges:
>>
>> *
>> _+ source db :_
>>
>> [[local]] thomasproot@serverconfig=# \dn+
>> List of schemas
>> ┌──┬┬───
>> ───┬┐
>> │   Name│   Owner │
>>Access
>> privileges │  Description│
>> ├──┼┼───
>> ───┼┤
>> │ public  │ serverconfig_owner│
>> serverconfig_owner=UC/serverconfig_owner↵   │
>> standard public schema   │
>> │   │  │
>> toolboxsysadmin=U/serverconfig_owner
>>  │  │
>> │ public_h  │ serverconfig_owner │
>> serverconfig_owner=UC/serverconfig_owner↵
>>  │  │
>> │   │  │
>> toolboxsysadmin=U/serverconfig_owner
>>  │  │
>> └──┴┴───
>> ───┴┘
>> (2 rows)
>>
>>
> So how did you revoke the privileges for PUBLIC in the above?
>
>
>> _+ target db _
>>
>> /schema of template1 :/
>>
>> thomasproot@template1=# \dn+
>>   List of schemas
>> ┌──┬───┬───┬─┐
>> │ Name │ Owner  │ Access privileges│ Description
>>│
>> ├──┼───┼───┼─┤
>> └──┴───┴───┴─┘
>> (0 rows)
>>
>>
>> /after restore:/*
>>
>> *
>> [[local]] thomasproot@serverconfig=# \dn+
>> List of schemas
>> ┌──┬┬───
>> ───┬┐
>> │ Name│   Owner │
>>  Access privileges │
>> Description │
>> ├──┼┼───
>> ───┼┤
>> │ public  │ serverconfig_owner│
>> serverconfig_owner=UC/serverconfig_owner   ↵│
>> standard public schema   │
>> │ 

Long running INSERT+SELECT query

[Vitaliy Garnashevich]

Hi,

We're trying to populate a table with aggregated data from other tables. 
For that we're running a huge INSERT+SELECT query which joins several 
tables, aggregates values, and then inserts the results into another 
table. The problem we're facing is that while the query is running , 
some records in tables referenced by the results table may be deleted, 
which causes the following error:


ERROR:  insert or update on table "..." violates foreign key constraint 
"..."

DETAIL:  Key (...)=(...) is not present in table "...".

Who do we make sure that such aggregating query would not fail?

Regards,
Vitaliy

Re: Long running INSERT+SELECT query

[Adrian Klaver]

On 04/26/2018 09:13 AM, Vitaliy Garnashevich wrote:

Hi,

We're trying to populate a table with aggregated data from other tables. 
For that we're running a huge INSERT+SELECT query which joins several 
tables, aggregates values, and then inserts the results into another 
table. The problem we're facing is that while the query is running , 
some records in tables referenced by the results table may be deleted, 
which causes the following error:


ERROR:  insert or update on table "..." violates foreign key constraint 
"..."

DETAIL:  Key (...)=(...) is not present in table "...".



SELECT .. FOR UPDATE?:

https://www.postgresql.org/docs/10/static/sql-select.html#SQL-FOR-UPDATE-SHARE

https://www.postgresql.org/docs/10/static/explicit-locking.html#LOCKING-ROWS


Who do we make sure that such aggregating query would not fail?

Regards,
Vitaliy






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

Re: Issue on public schéma with Pg_restore

[Adrian Klaver]

On 04/26/2018 07:52 AM, Thomas Poty wrote:

Hi,

About the minor version, we will plan the upgrade soon .

So how did you revoke the privileges for PUBLIC in the above? [db source]
=> After creation of the database, I executed:
     revoke all on schema public from public;


Hmm:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5955d934194c3888f30318209ade71b53d29777f

Not sure if this patch is in 9.6.8 or not. The interesting part is:

"We had some pretty ad-hoc handling of the public schema ..."

"Previously, while it was normally not mentioned, --clean mode would 
drop and recreate it, again causing headaches for non-superuser usage."


You might want to try without the --clean on the restore.



Thanks for the 'no-op' .

Thomas






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

Re: Long running INSERT+SELECT query

[Vitaliy Garnashevich]

SELECT .. FOR UPDATE?:

https://www.postgresql.org/docs/10/static/sql-select.html#SQL-FOR-UPDATE-SHARE 



https://www.postgresql.org/docs/10/static/explicit-locking.html#LOCKING-ROWS 



It says: "Currently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR 
KEY SHARE cannot be specified with GROUP BY."


Regards,
Vitaliy

Re: Long running INSERT+SELECT query

[Adrian Klaver]

On 04/26/2018 10:45 AM, Vitaliy Garnashevich wrote:



SELECT .. FOR UPDATE?:

https://www.postgresql.org/docs/10/static/sql-select.html#SQL-FOR-UPDATE-SHARE 



https://www.postgresql.org/docs/10/static/explicit-locking.html#LOCKING-ROWS 



It says: "Currently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR 
KEY SHARE cannot be specified with GROUP BY."


Without the query we are flying blind, so suggestions will have a ?



Regards,
Vitaliy



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

Re: Long running INSERT+SELECT query

[Vitaliy Garnashevich]

Without the query we are flying blind, so suggestions will have a ? 


Here is one such query:

    INSERT INTO cmdb_sp_usage_history
  (created_by, updated_by, created_on, updated_on, mod_count,
  summary_on, quarter, product, used_from, "user",
  keystrokes, minutes_in_use, times_started, avg_keystrokes, 
max_keystrokes, spkg_operational)

    SELECT
   2, 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 1,
   CURRENT_TIMESTAMP, quarter.id, "spv"."product", 
"usage"."used_from", "usage"."user",

   coalesce(sum("usage"."keystrokes"), 0),
   coalesce(sum("usage"."minutes_in_use"), 0),
   coalesce(sum("usage"."times_started"), 0),
   coalesce(avg("usage"."keystrokes"), 0),
   coalesce(max("usage"."keystrokes"), 0),
   bool_or("cmdb_ci"."operational")
    FROM
  "cmdb_program_daily_usage" "usage"
    LEFT OUTER JOIN
  "cmdb_program_instance" "p" ON "p"."id" = "usage"."program_instance"
    LEFT OUTER JOIN
  "cmdb_ci_spkg" "s" ON "s"."id" = "p"."spkg"
    LEFT OUTER JOIN
  "cmdb_ci" "cmdb_ci" ON "s"."id" = "cmdb_ci"."id"
    LEFT OUTER JOIN
  "cmdb_software_product_version" "spv" ON "spv"."id" = "s"."software"
    WHERE ("usage"."minutes_in_use" > 0)
  AND ((NOT ("s"."software" IS NULL))
   AND ((NOT ("s"."os" = TRUE))
    OR ("s"."os" IS NULL)))
  AND ("usage"."usage_date" >= quarter.start_date)
  AND ("usage"."usage_date" < quarter.end_date)
    GROUP BY "spv"."product", "usage"."used_from", "usage"."user"
    HAVING (coalesce(sum("usage"."keystrokes"), 0) > 0) OR 
(coalesce(sum("usage"."minutes_in_use"), 0) > 0) OR 
(coalesce(sum("usage"."times_started"), 0) > 0)

    ORDER BY "spv"."product", "usage"."used_from", "usage"."user";

Regards,
Vitaliy

Re: Long running INSERT+SELECT query

[Adrian Klaver]

On 04/26/2018 11:08 AM, Vitaliy Garnashevich wrote:


Without the query we are flying blind, so suggestions will have a ? 


Here is one such query:

     INSERT INTO cmdb_sp_usage_history
   (created_by, updated_by, created_on, updated_on, mod_count,
   summary_on, quarter, product, used_from, "user",
   keystrokes, minutes_in_use, times_started, avg_keystrokes, 
max_keystrokes, spkg_operational)

     SELECT
    2, 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 1,
    CURRENT_TIMESTAMP, quarter.id, "spv"."product", 
"usage"."used_from", "usage"."user",

    coalesce(sum("usage"."keystrokes"), 0),
    coalesce(sum("usage"."minutes_in_use"), 0),
    coalesce(sum("usage"."times_started"), 0),
    coalesce(avg("usage"."keystrokes"), 0),
    coalesce(max("usage"."keystrokes"), 0),
    bool_or("cmdb_ci"."operational")
     FROM
   "cmdb_program_daily_usage" "usage"
     LEFT OUTER JOIN
   "cmdb_program_instance" "p" ON "p"."id" = "usage"."program_instance"
     LEFT OUTER JOIN
   "cmdb_ci_spkg" "s" ON "s"."id" = "p"."spkg"
     LEFT OUTER JOIN
   "cmdb_ci" "cmdb_ci" ON "s"."id" = "cmdb_ci"."id"
     LEFT OUTER JOIN
   "cmdb_software_product_version" "spv" ON "spv"."id" = "s"."software"
     WHERE ("usage"."minutes_in_use" > 0)
   AND ((NOT ("s"."software" IS NULL))
    AND ((NOT ("s"."os" = TRUE))
     OR ("s"."os" IS NULL)))
   AND ("usage"."usage_date" >= quarter.start_date)
   AND ("usage"."usage_date" < quarter.end_date)
     GROUP BY "spv"."product", "usage"."used_from", "usage"."user"
     HAVING (coalesce(sum("usage"."keystrokes"), 0) > 0) OR 
(coalesce(sum("usage"."minutes_in_use"), 0) > 0) OR 
(coalesce(sum("usage"."times_started"), 0) > 0)

     ORDER BY "spv"."product", "usage"."used_from", "usage"."user";


Have not worked through all of the above, but a first draft suggestion:

Move the SELECT minus the aggregation functions into a sub-query that 
uses FOR UPDATE. Then do the aggregation on the results of the sub-query.




Regards,
Vitaliy




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

pg_stat_statements : how to catch non successfully finished statements ?

[legrand legrand]

Hello all,

I was wondering if there is a hook to collect non successfully finished SQL
statements in pg_stat_statements (timed-out, cancelled, killed, or simply
errored) ?

Thanks in advance
Regards
PAscal



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

Re: Long running INSERT+SELECT query

[Vitaliy Garnashevich]

Have not worked through all of the above, but a first draft suggestion:

Move the SELECT minus the aggregation functions into a sub-query that 
uses FOR UPDATE. Then do the aggregation on the results of the sub-query. 


The aggregated table has hundreds of millions of rows, and the query 
runs for many hours (which is one of the reasons why it's better not to 
fail). I really doubt that row level locking would work. That would be a 
lot of RAM just to hold all the locks.


On the other hand, I don't see something like FOR KEY SHARE kind of 
locks at table level, so that the query would try not to block most of 
other existing activity (e.g. SELECTs, UPDATEs).


Maybe this could be solved by calculating results into a temporary 
table, which would not check foreign key constraints, and then copy the 
data into the actual results table, while checking each row for FK 
consistency and skipping if necessary. But then I don't think it would 
be possible for my transaction to see row deletions which other 
transactions have done, and to check row existence (the transaction is 
there, because the whole thing is implemented as a DO statement with 
some local variables).


Thoughts?

Regards,
Vitaliy

PGAdmin4 debugger - unable to call ltree functions

[Ian Bell]

I have recently started working with the PGAdmin4 debugger and have
encountered a particular problem when testing my own functions that, in
turn, call functions from the ltree module.   The sample code below
successfully runs in  PSQL.  However, it fails when run in the PGAdmin4
debugger.   Specifically, the debugger generates the following error message
when it executes the first call to 'text2ltree':

 

ERROR: syntax error at position 0 

CONTEXT: SQL statement "SELECT exists ( select 1 from TableLtree where
MyPath = text2ltree( MyArg ) )" 

PL/pgSQL function testltree(text) line 5 at IF

 

I have successfully been able to use the debugger to walk through many of my
test functions providing they only use standard SQL variables.   However, I
am unable to debug code that calls the ltree functions.  

 

Is this a problem/bug with the debugger or am I doing something wrong?  If I
am doing something wrong then can you tell me what it is?

 

Thank you,

 

Ian

 

 

Sample Code in a SQL file:

 

create extension if not exists ltree;

create extension if not exists pldbgapi;

 

create table if not exists TableLtree(

 ID int 

  primary key generated by default as identity,

 MyPath ltree

);

 

 

 

 

 

create or replace function TestLtree(

 MyArg text

) 

returns void

as $$

declare

 status boolean;

begin

 if exists ( select 1 from TableLtree where MyPath = text2ltree( MyArg )
) then

   status := true;

 else

   status := false;

 end if;

 

 if status = false then

   insert into TableLtree( MyPath ) values ( text2ltree( MyArg ) );

 end if;

end;

$$ language plpgsql;

 

 

 

 

 

select * from TestLtree( 'a.b.c' );

 

 

 

Re: Long running INSERT+SELECT query

[David G. Johnston]

On Thu, Apr 26, 2018 at 1:32 PM, Vitaliy Garnashevich <
vgarnashev...@gmail.com> wrote:

> The aggregated table has hundreds of millions of rows, and the query runs
> for many hours (which is one of the reasons why it's better not to fail)
>
​[...]​


> Maybe this could be solved by calculating results into a temporary table,
> which would not check foreign key constraints, and then copy the data into
> the actual results table, while checking each row for FK consistency and
> skipping if necessary.
>

Me, I'd try very hard to design things so the final calculation goes into
an actual results table that omits FK constraints​ and wouldn't try to
"skip if necessary".  You are already running an hours-long query - the
users of said information needs to understand that what they are seeing
does not reflect changes in the subsequent hour(s) since it started and
that certain related records being no longer present doesn't detract from
the fact that they were present "back then" and thus represent valid data
at that point in time.

David J.

Re: Long running INSERT+SELECT query

[Alvaro Herrera]

Vitaliy Garnashevich wrote:
> 
> > Have not worked through all of the above, but a first draft suggestion:
> > 
> > Move the SELECT minus the aggregation functions into a sub-query that
> > uses FOR UPDATE. Then do the aggregation on the results of the
> > sub-query.
> 
> The aggregated table has hundreds of millions of rows, and the query runs
> for many hours (which is one of the reasons why it's better not to fail). I
> really doubt that row level locking would work. That would be a lot of RAM
> just to hold all the locks.

Row locks are not stored in memory.

Of course, a FOR KEY SHARE lock would block DELETEs that try to remove
the locked row.

I think your proposed strategy of trying to merge what other processes
did while you were copying is very problematic.

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

Re: PGAdmin4 debugger - unable to call ltree functions

[Adrian Klaver]

On 04/26/2018 01:22 PM, Ian Bell wrote:
I have recently started working with the PGAdmin4 debugger and have 
encountered a particular problem when testing my own functions that, in 
turn, call functions from the ltree module.   The sample code below 
successfully runs in  PSQL.  However, it fails when run in the PGAdmin4 
debugger.   Specifically, the debugger generates the following error 
message when it executes the first call to ‘*text2ltree*’:


ERROR: syntax error at position 0

CONTEXT: SQL statement "SELECT exists ( select 1 from TableLtree where 
MyPath = text2ltree( MyArg ) )"


PL/pgSQL function testltree(text) line 5 at IF

I have successfully been able to use the debugger to walk through many 
of my test functions providing they only use standard SQL variables.   
However, I am unable to debug code that calls the ltree functions.


Are they all using the 'IF exists ..' construct?

What if you try a very simple function, something like(not tested):

create or replace function TestLtree2(MyArg text)

returns void

as $$

declare

 _testvar integer;

begin

SELECT 1 INTO _testvar FROM TableLtree WHERE MyPath = text2ltree( 
MyArg ) LIMIT 1;


end;

$$ language plpgsql;




Is this a problem/bug with the debugger or am I doing something wrong?  
If I am doing something wrong then can you tell me what it is?


Thank you,

Ian

*_Sample Code in a SQL file:_*

create extension if not exists ltree;

create extension if not exists pldbgapi;

create table if not exists TableLtree(

  ID int

   primary key generated by default as identity,

  MyPath ltree

);

create or replace function TestLtree(

  MyArg text

)

returns void

as $$

declare

  status boolean;

begin

  if exists ( select 1 from TableLtree where MyPath = text2ltree( 
MyArg ) ) then


    status := true;

  else

    status := false;

  end if;

  if status = false then

    insert into TableLtree( MyPath ) values ( text2ltree( MyArg ) );

  end if;

end;

$$ language plpgsql;

select * from TestLtree( 'a.b.c' );




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

Re: Long running INSERT+SELECT query

[Adrian Klaver]

On 04/26/2018 01:32 PM, Vitaliy Garnashevich wrote:



Have not worked through all of the above, but a first draft suggestion:

Move the SELECT minus the aggregation functions into a sub-query that 
uses FOR UPDATE. Then do the aggregation on the results of the sub-query. 


The aggregated table has hundreds of millions of rows, and the query 
runs for many hours (which is one of the reasons why it's better not to 
fail). I really doubt that row level locking would work. That would be a 
lot of RAM just to hold all the locks.


On the other hand, I don't see something like FOR KEY SHARE kind of 
locks at table level, so that the query would try not to block most of 
other existing activity (e.g. SELECTs, UPDATEs).


Maybe this could be solved by calculating results into a temporary 
table, which would not check foreign key constraints, and then copy the 
data into the actual results table, while checking each row for FK 
consistency and skipping if necessary. But then I don't think it would 
be possible for my transaction to see row deletions which other 
transactions have done, and to check row existence (the transaction is 
there, because the whole thing is implemented as a DO statement with 
some local variables).


Thoughts?


The procedure seems to be fighting itself. There is an inherent conflict 
between trying to keep up with data changes and presenting a consistent 
result. Keeping up means constantly updating the aggregation 
calculations which in turn means the result will continually changing. 
As David and Alvaro have also suggested your best bet is to pick a point 
in time and work off that.




Regards,
Vitaliy





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

RE: PGAdmin4 debugger - unable to call ltree functions

[Ian Bell]

Hello Adrian,

 

I just tried your suggestion and it generates the same error.  For
verification, here is the function run in the debugger.

 

create or replace function TestLtreeV2( MyArg text ) returns void

as $$

declare

 _testVar integer;

begin

SELECT 1 INTO _testvar FROM TableLtree WHERE MyPath = text2ltree( MyArg )
LIMIT 1;

raise notice '_testVar = %', _testVar;

end;

$$ language plpgsql;

 

Ian

 

 

 

 

 

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: 26 April, 2018 17:23
To: i...@ianbellsoftware.com  ;
pgsql-general@lists.postgresql.org
 
Subject: Re: PGAdmin4 debugger - unable to call ltree functions

 

On 04/26/2018 01:22 PM, Ian Bell wrote:

> I have recently started working with the PGAdmin4 debugger and have 

> encountered a particular problem when testing my own functions that, 

> in turn, call functions from the ltree module.   The sample code below 

> successfully runs in  PSQL.  However, it fails when run in the 

> PGAdmin4 debugger.   Specifically, the debugger generates the 

> following error message when it executes the first call to '*text2ltree*':

> 

> ERROR: syntax error at position 0

> 

> CONTEXT: SQL statement "SELECT exists ( select 1 from TableLtree where 

> MyPath = text2ltree( MyArg ) )"

> 

> PL/pgSQL function testltree(text) line 5 at IF

> 

> I have successfully been able to use the debugger to walk through many 

> of my test functions providing they only use standard SQL variables.   

> However, I am unable to debug code that calls the ltree functions.

 

Are they all using the 'IF exists ..' construct?

 

What if you try a very simple function, something like(not tested):

 

create or replace function TestLtree2(MyArg text)

 

returns void

 

as $$

 

declare

 

  _testvar integer;

 

begin

 

 SELECT 1 INTO _testvar FROM TableLtree WHERE MyPath = text2ltree( MyArg
) LIMIT 1;

 

end;

 

$$ language plpgsql;

 

 

> 

> Is this a problem/bug with the debugger or am I doing something wrong?  

> If I am doing something wrong then can you tell me what it is?

> 

> Thank you,

> 

> Ian

> 

> *_Sample Code in a SQL file:_*

> 

> create extension if not exists ltree;

> 

> create extension if not exists pldbgapi;

> 

> create table if not exists TableLtree(

> 

>   ID int

> 

>primary key generated by default as identity,

> 

>   MyPath ltree

> 

> );

> 

> create or replace function TestLtree(

> 

>   MyArg text

> 

> )

> 

> returns void

> 

> as $$

> 

> declare

> 

>   status boolean;

> 

> begin

> 

>   if exists ( select 1 from TableLtree where MyPath = text2ltree( 

> MyArg ) ) then

> 

> status := true;

> 

>   else

> 

> status := false;

> 

>   end if;

> 

>   if status = false then

> 

> insert into TableLtree( MyPath ) values ( text2ltree( 

> MyArg ) );

> 

>   end if;

> 

> end;

> 

> $$ language plpgsql;

> 

> select * from TestLtree( 'a.b.c' );

> 

 

 

--

Adrian Klaver

  adrian.kla...@aklaver.com

Re: PGAdmin4 debugger - unable to call ltree functions

[Adrian Klaver]

On 04/26/2018 02:54 PM, Ian Bell wrote:

Hello Adrian,

I just tried your suggestion and it generates the same error.  For 
verification, here is the function run in the debugger.


create or replace function TestLtreeV2( MyArg text ) returns void

as $$

declare

  _testVar integer;

begin

SELECT 1 INTO _testvar FROM TableLtree WHERE MyPath = text2ltree( MyArg 
) LIMIT 1;


raise notice '_testVar = %', _testVar;

end;

$$ language plpgsql;


I'm tapped out. You might want to get hold of the pgAdmin folks directly:

https://www.pgadmin.org/support/list/




Ian



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

Re: PGAdmin4 debugger - unable to call ltree functions

[Tom Lane]

"Ian Bell"  writes:
> I just tried your suggestion and it generates the same error.  For
> verification, here is the function run in the debugger.

I just realized that "syntax error at position N" is *not* the exact
spelling you would get from a syntax error in PG's main parser, which
I think is what we were all assuming this'd trace down to somehow.

What it is is the (very poorly chosen) message you get if either
ltree_in() or lquery_in() doesn't like what it's been handed.
So basically, the problem here is that TestLtree() is being called
with a string that isn't a valid ltree representation.  You haven't
shown us what you are doing on that end, but for example I can
duplicate the error message with

# select text2ltree(' ');
ERROR:  syntax error at position 0

regards, tom lane

RE: PGAdmin4 debugger - unable to call ltree functions

[Ian Bell]

My function TestLtree() is being executed in the PGAdmin debugger.   The
debugger requires I define the argument being passed, I set this to 'a.b.c'
and I have confirmed in the debugger that this is indeed what is passed into
the function.

I do not understand what you mean by "You haven't shown us what you are
doing on that end".   I am only using the debugger to test a simple function
- nothing more.

As mentioned before, the code runs without problems in both PSQL and in
PGAdmin4 - it only fails when executed in the debugger.  

BTW, I'm running PostgreSQL 10.3, compiled by Visual C++ build 1800, 64-bit
under Windows 10.

Thank,

Ian


-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us] 
Sent: 26 April, 2018 18:18
To: i...@ianbellsoftware.com
Cc: 'Adrian Klaver' ;
pgsql-general@lists.postgresql.org
Subject: Re: PGAdmin4 debugger - unable to call ltree functions

"Ian Bell"  writes:
> I just tried your suggestion and it generates the same error.  For 
> verification, here is the function run in the debugger.

I just realized that "syntax error at position N" is *not* the exact
spelling you would get from a syntax error in PG's main parser, which I
think is what we were all assuming this'd trace down to somehow.

What it is is the (very poorly chosen) message you get if either
ltree_in() or lquery_in() doesn't like what it's been handed.
So basically, the problem here is that TestLtree() is being called with a
string that isn't a valid ltree representation.  You haven't shown us what
you are doing on that end, but for example I can duplicate the error message
with

# select text2ltree(' ');
ERROR:  syntax error at position 0

regards, tom lane

Re: PGAdmin4 debugger - unable to call ltree functions

[Tom Lane]

"Ian Bell"  writes:
> My function TestLtree() is being executed in the PGAdmin debugger.   The
> debugger requires I define the argument being passed, I set this to 'a.b.c'
> and I have confirmed in the debugger that this is indeed what is passed into
> the function.

> I do not understand what you mean by "You haven't shown us what you are
> doing on that end".   I am only using the debugger to test a simple function
> - nothing more.

Well, I'd say that *something* is calling that function with an argument
different from what you expect.  It's entirely possible that this is a
bug in PGAdmin's debugger.  Again, you'd be best off discussing it on the
pgadmin lists rather than here.

regards, tom lane

Parameter passing in trigger function write in C

[a]

Hey everyone:


I was request to write a trigger that perform the following function:


1, The trigger will be set to exam all statement that will result change of 
datas


2, The trigger will exam three information other than the data updated or 
inserted: user name, password and reasons of change


3, It will record down, in some format, the time and content of the change.


Therefore, I would need some way of passing parameters into the trigger 
function. Can anyone give me some instructions on how to do it or, if it is 
possible??

Re: Parameter passing in trigger function write in C

[Adrian Klaver]

On 04/26/2018 06:13 PM, a wrote:

Hey everyone:

I was request to write a trigger that perform the following function:

1, The trigger will be set to exam all statement that will result change 
of datas


2, The trigger will exam three information other than the data updated 
or inserted: user name, password and reasons of change


3, It will record down, in some format, the time and content of the change.

Therefore, I would need some way of passing parameters into the trigger 
function. Can anyone give me some instructions on how to do it or, if it 
is possible??


https://www.postgresql.org/docs/10/static/trigger-interface.html


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

Re: Parameter passing in trigger function write in C

[a]

hey thanks mate, I have red that. But the actual data retirement seems to be 
combination of that and the SPI_get* functions. I'm still testing on it. By the 
way, if I can pass parameters that is out of the scope of sql statement??




-- Original --
From: "Adrian Klaver"; 
Date: Friday, Apr 27, 2018 9:36 AM
To: "a"<372660...@qq.com>; "pgsql-general"; 
Subject: Re: Parameter passing in trigger function write in C



On 04/26/2018 06:13 PM, a wrote:
> Hey everyone:
> 
> I was request to write a trigger that perform the following function:
> 
> 1, The trigger will be set to exam all statement that will result change 
> of datas
> 
> 2, The trigger will exam three information other than the data updated 
> or inserted: user name, password and reasons of change
> 
> 3, It will record down, in some format, the time and content of the change.
> 
> Therefore, I would need some way of passing parameters into the trigger 
> function. Can anyone give me some instructions on how to do it or, if it 
> is possible??

https://www.postgresql.org/docs/10/static/trigger-interface.html


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