Re: rollback previous commit if the current one fails

[pinker]

thank you Luis, but this is not supported in plpgsql



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

rollback previous commit if the current one fails

[pinker]

Hi,
i need to emulate oracle's savepoint behaviour inside of the plpgsql
function.

This function is able to insert all the rows that weren't caught on the
exception, but i need also to rollback the insert that happens before the
exception.

So let's say the exception is thrown when j=3 so i need also to rollback
j=2.
Any idea how to approach it?

DROP TABLE IF EXISTS test;
CREATE TABLE test
(
id INT
);

CREATE OR REPLACE PROCEDURE test()
AS
$$
DECLARE
j INT;
BEGIN

FOR j IN 0..6
LOOP
BEGIN
INSERT INTO test VALUES (1 / j);
EXCEPTION
WHEN OTHERS THEN
END;
END LOOP;
END;
$$ LANGUAGE plpgsql;

CALL test();
TABLE test;



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

Re: insert on conflict postgres returning distinction

[pinker]

Od course inside transaction block



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

Re: insert on conflict postgres returning distinction

[pinker]

how about this solution?
Does it have any caveats?

WITH upsert AS (INSERT INTO GUCIO (ID, NAZWA)
SELECT A.ID, A.NAZWA
FROM ALA A
ON CONFLICT (ID) DO UPDATE SET
nazwa = excluded.nazwa
RETURNING xmax,xmin, *)
select xmax as xmax_value
into txmaxu
from upsert;

delete
from gucio
where xmax = (select xmax_value from txmaxu where xmax_value <> 0 limit 1);



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

Re: insert on conflict postgres returning distinction

[pinker]

thank you Adrian,
the background of it is that I have already written the python script that
translates Oracle MERGE clause to Postgres INSERT ... ON CONFLICT, but in
order to be able to add DELETE part from MERGE i need to distinct those
operations. 

thank you for the idea with trigger, i haven't thought about it at the
beginning, but it does complicate the code a lot :/ not saying about
performance... 



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

insert on conflict postgres returning distinction

[pinker]

is there any way to distinct between updated and inserted rows in RETURNING
clause when ON CONFLICT UPDATE was used?



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

Re: COPY blocking \dt+?

[pinker]

thank you David.
So it would need to run inside single transaction to cause lock, right? do
you know if pgbench is opening transaction?



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

COPY blocking \dt+?

[pinker]

Hi,
I'm running standard pgbench and what's kinda strange copy pgbench_accounts
from stdin is blocking my other query which is \dt+.
Does copy hold any exclusive lock or there is something wrong with my
system?
i'm using pgbench=>  SELECT version();
-[ RECORD 1 ]---
version | PostgreSQL 11.6, compiled by Visual C++ build 1800, 64-bit



-[ RECORD 1
]+--

datid| 27483
datname  | pgbench
pid  | 931408
usesysid | 14419
usename  | gucio
application_name | pgbench
client_addr  | 212.180.214.106
client_hostname  | 
client_port  | 23041
backend_start| 2020-05-05 00:47:12.182801+00
xact_start   | 2020-05-05 00:47:12.542264+00
query_start  | 2020-05-05 00:53:26.402305+00
state_change | 2020-05-05 00:53:26.402305+00
wait_event_type  | 
wait_event   | 
state| active
backend_xid  | 3919
backend_xmin | 3919
query| copy pgbench_accounts from stdin
backend_type | client backend
-[ RECORD 2
]+--

datid| 27483
datname  | pgbench
pid  | 932736
usesysid | 14419
usename  | gucio
application_name | psql
client_addr  | 212.180.214.106
client_hostname  | 
client_port  | 8718
backend_start| 2020-05-05 00:48:10.031429+00
xact_start   | 2020-05-05 00:56:34.324414+00
query_start  | 2020-05-05 00:56:34.324414+00
state_change | 2020-05-05 00:56:34.324414+00
wait_event_type  | Lock
wait_event   | relation
state| active
backend_xid  | 
backend_xmin | 3919
query| SELECT n.nspname as "Schema",


+
 |   c.relname as "Name",   


+
 |   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN
'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN
'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table
' WHEN 'p' THEN 'table' WHEN 'I' THEN 'index' END as "Type",+
 |   pg_catalog.pg_get_userbyid(c.relowner) as "Owner", 


+
 |  
pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",   

   
+
 |   pg_catalog.obj_description(c.oid, 'pg_class') as
"Description"   
  
+
 | FROM pg_catalog.pg_class c   


+
 |  LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
c.relnamespace  
 
+
 | WHERE c.relkind IN ('r','p','')  


+
 |   AND n.nspname <> 'pg_catalog'  


+
 |   AND n.nspname <> 'information_schema'  
 

1GB of maintenance work mem

[pinker]

Hi,
is this limit for maintenance work mem still there? or it has been patched?

https://www.postgresql-archive.org/Vacuum-allow-usage-of-more-than-1GB-of-work-mem-td5919221i180.html



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

Re: Loading 500m json files to database

[pinker]

hmm now I'm thinking maybe setting up pgbouncer in front of postgres with
statement mode would help?



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

Re: Loading 500m json files to database

[pinker]

it's in a blob storage in Azure. I'm testing with 1m that I have locally



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

Re: Loading 500m json files to database

[pinker]

Hi,
json_parts it's just single table with 2 column:

  Table "public.json_parts"
  Column   |  Type   | Collation | Nullable |Default

| Storage  | Stats target | Description 
---+-+---+--++--+--+-
 id| integer |   | not null |
nextval('json_parts_id_seq'::regclass) | plain|  | 
 json_data | jsonb   |   |  |  

no indexes, constraints or anything else



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

Re: Loading 500m json files to database

[pinker]

there is no indexes nor foreign keys, or any other constraints



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

Re: Loading 500m json files to database

[pinker]

it's a cloud and no plpythonu extension avaiable unfortunately



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

Re: Loading 500m json files to database

[pinker]

Ertan Küçükoğlu wrote
> However, if possible, you may think of using a local physical computer to
> do all uploading and after do backup/restore on cloud system.
> 
> Compressed backup will be far less internet traffic compared to direct
> data inserts.

I was thinking about that but data source is a blob storage, so downloading
it first and then loading locally it's couple days extra for processing :/
it's not that fast even when I'm doing it locally ... so that would be like
extra 2 steps overhead :/



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

Re: Loading 500m json files to database

[pinker]

Christopher Browne-3 wrote
> Well, you're paying for a lot of overhead in that, as you're
> establishing a psql command, connecting to a database, spawning a backend
> process, starting a transactions, committing a transaction, closing the
> backend
> process, disconnecting from the database, and cleaning up after the
> launching
> of the psql command.  And you're doing that 500 million times.
> 
> The one thing I left off that was the loading of a single tuple into
> json_parts.
> 
> What you could do to improve things quite a lot would be to group some
> number
> of those files together, so that each time you pay for the overhead, you
> at
> least
> get the benefit of loading several entries into json_parts.
> 
> So, loosely, I'd commend using /bin/cat (or similar) to assemble several
> files together
> into one, and then \copy that one file in.
> 
> Having 2 tuples loaded at once drops overhead by 50%
> Having 10 tuples loaded at once drops overhead by 90%
> Having 100 tuples loaded at once drops overhead by 99%
> Having 1000 tuples loaded at once drops overhead by 99.9%
> 
> There probably isn't too much real value to going past 1000 tuples per
> batch; the
> overhead, by that point, is getting pretty immaterial.
> 
> Reducing that overhead is the single most important thing you can do.

Yes, I was thinking about that but no idea now how to do it right now. like
some kind of outer loop to concatenate those files? and adding delimiter
between them?


Christopher Browne-3 wrote
> It is also quite likely that you could run such streams in parallel,
> although
> it would require quite a bit more information about the I/O capabilities
> of
> your
> hardware to know if that would do any good.

I can spin up every size of instance.





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

Loading 500m json files to database

[pinker]

Hi, do you have maybe idea how to make loading process faster?

I have 500 millions of json files (1 json per file) that I need to load to
db.
My test set is "only" 1 million files.

What I came up with now is:

time for i in datafiles/*; do
  psql -c "\copy json_parts(json_data) FROM $i"&
done

which is the fastest so far. But it's not what i expect. Loading 1m of data
takes me ~3h so loading 500 times more is just unacceptable.

some facts:
* the target db is on cloud so there is no option to do tricks like turning
fsync off
* version postgres 11
* i can spin up huge postgres instance if necessary in terms of cpu/ram
* i tried already hash partitioning (to write to 10 different tables instead
of 1)


Any ideas?



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

Re: pg_sleep() inside plpgsql block - pro & cons

[pinker]

Francisco Olarte wrote
> I do some similar things, but I sleep outside of the
> database, is there a reason this can not be done?
> 
> Francisco Olarte.

Yes, I do try to convince them to do it outside the db, that's the reason
I'm looking for some support here :) I'm not sure those 2 reasons are enough
to convince them, wanted to be prepared...
You know it's always time to do refactoring and in this case it's a lot of
time because of the amount and complexity of the logic in db.




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

pg_sleep() inside plpgsql block - pro & cons

[pinker]

Hi!
There is second time I see that somebody uses pg_sleep function inside
plpgsql block. This case is quite similar to the last one - it's some kind
of wait for data to be loaded. After pg_sleep there is a check if some
condition is true, if not procedure goes to sleep again. As a result an
average duration of this function is 1,5h...
I'm trying to gather pros and cons regarding using pg_sleep this way. What's
coming to my mind are only 2 cons:
* clog contention
* long running open transactions (which is quite good described in here:
https://www.simononsoftware.com/are-long-running-transactions-bad/)

So maybe you'll add some more to the list?



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

Re: Why the sql is not executed in parallel mode

[pinker]

Which version are you running?



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

Re: Postgresql

[pinker]

You might find this comparision useful:
https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems



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

Re: List user who have access to schema

[pinker]

I think this one will give you report you need:

select schema_name, 
roleid::regrole, 
string_agg(member::regrole::text,',' order by member::regrole::text) users
from information_schema.schemata s, pg_user u
JOIN pg_auth_members a ON u.usename::text=a.roleid::regrole::text
WHERE s.schema_name not like 'pg_%' AND has_schema_privilege(usename,
s.schema_name, 'usage')
GROUP BY s.schema_name, roleid::regrole, u.usename
order by 1;



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

Re: FK v.s unique indexes

[pinker]

David Rowley-3 wrote
> I don't think there were any actual roadblocks, it was more of just
> not enough time in the cycle to make it work due to a few technical
> details that required extra effort to make work.
> 
> Alvaro managed to simplify the problem and allow foreign keys to be
> defined on partitioned tables and get that into PG11.
> 
> So it was a case of 50% is better than 0%, which I very agree with.

That's a really great news. I was waiting for this feature for many years.
Finally! Thank you guys!



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

Re: except all & WITH - syntax error?

[pinker]

thank you for the answer, had no idea about "syntactic precedence" thing.



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

Re: inner join elimination

[pinker]

As far as I know PostgreSQL does only OUTER JOIN Elimination, with inner join
it doesn't work.



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

Plans cache eviction from process local Memory

[pinker]

Hi!
I would like to know how postgres will behave with a big amount of cached
plans from prepared statements on 1 connection. Let's say there is an
application level connection pooling and one connection can last for many
weeks. Many plans are cached from many prepared statements, is there any
postgres management of that or it is totally system thing? 
Some plans will be evicted from process memory at some point of time or they
will be kept infinitely causing huge amount of memory being allocated for
this connection?



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

Re: decompose big queries

[pinker]

Edson Carlos Ericksson Richter wrote
> I don't know if there are best practices (each scenario requires its own 
> solution), but for plain complex SELECT queries, I do use "WITH" 
> queries... They work really well.

Be cautious with CTE's. They weren't meant to be an alternative to
subqueries and will probably change the way your query is executed, because
they are optimisation fences:
https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/



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

Re: unreliable behaviour of track_functions

[pinker]

and to be clear I was experimenting with pg_stat_clear_snapshot() after your
answer, but it doesn't change behaviour of track_functions.



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

Re: unreliable behaviour of track_functions

[pinker]

I mean this part describing track_function:

https://www.postgresql.org/docs/10/static/runtime-config-statistics.html

Enables tracking of function call counts and time used. Specify pl to track
only procedural-language functions, all to also track SQL and C language
functions. The default is none, which disables function statistics tracking.
Only superusers can change this setting.

Note
SQL-language functions that are simple enough to be “inlined” into the
calling query will not be tracked, regardless of this setting.

Only case described here, that exclude function from being tracked it's
inlining, not the time and not the place in the query.
So I would expect that pg_stat_user_function will show me that my function
was executed. Good that are other ways to do it, but changing
track_functions to 'all' I would expect all calls will be tracked...



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

unreliable behaviour of track_functions

[pinker]

Hi All!

I've been experimenting with track_functions options and what I've saw it's
really puzzling me.
Documentation says: 
/ SQL-language functions that are simple enough to be "inlined" into the
calling query will not be tracked, regardless of this setting./

But it came up, it depends on much more factors, like duration or placing it
in the query, it is totally non-deterministic behaviour.

This really simple SQL function:
CREATE FUNCTION a(a bigint)
  RETURNS bigint
STABLE
LANGUAGE SQL
AS $$
SELECT $1
$$;

Is not shown in the pg_stat_user_functions at all. It is started to be shown
when one line:
select pg_sleep(1);

is added???

Another one, gets tracked only if I use:
SELECT get_unique_term(2556);

If it lands in FROM then it's not tracked...
SELECT * FROM get_unique_term(2556);

That's the body of the function:
CREATE FUNCTION get_unique_term(i_game_pin bigint)
  RETURNS TABLE(term text, category text)
STABLE
LANGUAGE SQL
AS $$
SELECT
  i.term,
  i.dict_category_id
FROM (SELECT
categories.term,
categories.dict_category_id
  FROM categories
  EXCEPT ALL
  SELECT
games.term,
games.category
  FROM games
  WHERE game_pin = $1) i
ORDER BY (random())
LIMIT 1;
$$;

What's going on here? That's pretty unreliable behaviour...


My version of postgres:
 PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3
20140911 (Red Hat 4.8.3-9), 64-bit

show track_functions;
 track_functions 
-
 all
(1 wiersz)



 




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

Re: SELECT .. FOR UPDATE: find out who locked a row

[pinker]

Check out here: https://wiki.postgresql.org/wiki/Lock_Monitoring



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

Re: Need followup setup instructions to postgresql-10.3-1-linux-x64.run

[pinker]

you probably need to change pg_hba.conf. set the authentication method to
trust for your user, reload the server with pg_ctl, go to psql and change
the passwords. more details you will find here:

https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html



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

Re: Best options for new PG instance

[pinker]

Bugzilla from scher...@proteus-tech.com wrote
> Oh - and lots of memory is always good no matter what as others have said.

I'm probably "the others" here. I have seen already really large
instalations like with 6TB of RAM. Dealing with it is like completely other
universe of problems, because of NUMA - you cannot really have large RAM
without multiple sockets, because every processor has got maximum memory
capacity. What's next - those processors need to communicate with each other
and the hardware and those algorithms aren't perfect yet (would rather say
are underdeveloped).

so - more memory is a good rule of thumb, but sky isn't the limit :)



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

Re: dirty_ratio & dirty_background_ratio settings with huge memory

[pinker]

Laurenz Albe wrote
> Yes, you should set vm.dirty_background_bytes and vm.dirty_bytes
> and not use the *_ratio settings.
> 
> 2 GB for vm.dirty_background_bytes and 1 GB for vm.dirty_bytes sounds
> fine.
> 
> Yours,
> Laurenz Albe
> -- 
> Cybertec | https://www.cybertec-postgresql.com

Thank you Laurenz. I was always wondering why not to set bigger window
between those 2. Because setting dirty_background_bytes seems quite natural
for me, i.e. start to write asynchronously faster, but why to provoke stalls
faster? is it not better to stretch the window much wider, like 1GB for
dirty_background_bytes and for instance 20 GB dirty_bytes? it's the Approach
3: Both Ways from
https://lonesysadmin.net/2013/12/22/better-linux-disk-caching-performance-vm-dirty_ratio/



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

Re: dirty_ratio & dirty_background_ratio settings with huge memory

[pinker]

Andres Freund wrote
> With a halfway modern PG I'd suggest to rather tune postgres settings
> that control flushing. That leaves files like temp sorting in memory for
> longer, while flushing things controlledly for other sources of
> writes. See *_flush_after settings.
> 
> Greetings,
> 
> Andres Freund

Thank you Andres, I missed those parameters appearing. It's not the case for
this customer, because they have an ancient version 9.0, but it's
interesting for others :)
The maximal value - 2MB - is it not to small? Regarding the fact that for
instance the same buffer will be updated 10 times between checkpoints and
bgwriter well be flushing it directly to disk 10 times instead to allow the
buffer to stay in the cache and do the update faster?




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

dirty_ratio & dirty_background_ratio settings with huge memory

[pinker]

Hi,
I've got cutomer with really huge RAM, now it's:
 total   used   free sharedbuffers cached
Mem:   31021113052596  49515 2088019922961185
-/+ buffers/cache:  904183011693
Swap: 8191  1   8190

(free -m)

and before it was twice more (6TB).

and trying to figure out how to set dirty_ratio & dirty_background_ratio
parameters. Even for normal sized server those default sizes are to high,
but now would be ridiculously huge, respectively 1,24TB and 300GB. 
I'm thinking about 1 percent for dirty_ratio and not using
dirty_background_ratio because it's not possible to set it below 1% but to
set dirty_background_bytes instead to about 1-2GB.
Maybe someone has got other experience with RAM of this size and those
settings?





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

Re: Possible bug: could not open relation with OID [numbers] SQL State: XX000

[pinker]

I would like to refresh the topic and add another report about the issue that
just happened to me.I'm sure it's the toast table that cannot be opened
inside the function.I have added following RAISE NOTICE clauses to it and
run analyze inside of the function:  
analyze verbose temp_table; 
raise notice 'oid temp_table %', ( SELECT array_agg(relname::TEXT||
relfilenode::TEXT|| 'relpages:'||relpages::TEXT|| 'reltuples:' ||
reltuples::TEXT|| 'relallvisible:' ||relallvisible::TEXT||'reltoastrelid:'||
reltoastrelid::TEXT) FROM pg_class where relname= 'temp_table');
raise notice 'rel size %', (select
pg_total_relation_size('temp_table'));
It's pointing to the toast table:
1 live rows and 1 dead rows; 1 rows in sample, 1 estimated total
rowspsql:/tmp/gg:23: NOTICE:  oid temp_table
{temp_table106538relpages:1reltuples:1relallvisible:0reltoastrelid:*106541*}psql:/tmp/gg:23:
NOTICE:  rel size 32768psql:/tmp/gg:23: ERROR:  could not open relation with
OID *106541*
Thank you for the advice about ON COMMIT DROP - it's working.When the table
size is smaller, about 16k this issue simply disappears.



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

Re: pg_basebackup is taking more time than expected

[pinker]

It will not solve the problem, but maybe try with --checkpoint=fast option.



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

Re: How Many Partitions are Good Performing

[pinker]

I've run once a test on my laptop because was curious as well. From my
results (on laptop - 16GB RAM, 4 cores) the upper limit was 12k. Above it
planning time was unbearable high - much higher than execution time. It's
been tested on 9.5



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