increase of xact_commit vs txid_current

[reg_pg_stefanz]

Hi,

I am confused, the documentation says for pg_stat_database
   xact_commit      Number of transactions in this database that have 
been committed

and somewhere else
    txid_current()    get current transaction ID, assigning a new one 
if the current transaction does not have one


I would have naively expected txid_current() be more or less in line 
with xact_commit,  or  increasing faster as txid_current() should be 
global but xact_commit is per database.


However xact_commit seems to increases faster than txid_current(),  what 
am I missing? Are there commits that do not increase the xid number?


Thanks
Stefan

delete on table with many partitions uses a lot of ram

[reg_pg_stefanz]

Hi,

I noticed that a delete on a table with many partitions seems to be 
using a lot of ram.
It seems to occur during the planing phase, as  explain behaves the same 
as the actual execution of the delete.


On the simplified test below for 4000 partitions it seems to be using 
for a short time over 5Gb of Memory,  as if for each partition more than 
1 MB of Ram is allocated,
if a few concurrent sessions are doing this, the server is fast running 
out of memory


Only a delete is showing this behaviour, insert or select do not; I have 
not tested update.

Is this a known behaviour or related to my setup?

Versions 10, 11, even 12 complied from github source, showed similar 
behaviour.


Regards
Stefan


a simplified test with 4000 partitions:

drop table if exists big;

CREATE TABLE big (i int, j int)
 PARTITION BY RANGE (i);

CREATE TABLE big_0  PARTITION OF big
   FOR VALUES FROM (-1) TO (0);
CREATE INDEX ON big_0 (i);

do $$
DECLARE
 v_part varchar(100);
 v_sql TEXT;
 r record;
 dt date;
begin
  for r in (select generate_series(1,4000,1) nr)
    loop
   v_part:='big_'||r.nr;
   v_sql := format( 'CREATE TABLE %s PARTITION OF %s
 FOR VALUES FROM (''%s'') TO (''%s'');
 CREATE INDEX ON %s (i);',
 v_part,'big',
 r.nr-1,r.nr,
 v_part);
    EXECUTE v_sql;
 end loop;
 END;
$$ LANGUAGE plpgsql;

select name, setting, short_desc from pg_settings
  where name in ('max_connections','max_locks_per_transaction');

begin;

select locktype, virtualtransaction, pid, mode, granted, fastpath, count(*)
  from pg_locks
 group by locktype, virtualtransaction, pid, mode, granted, fastpath;

-- delete from big where i=3 and j=0;
explain delete from big where i=3 and j=0;

select locktype, virtualtransaction, pid, mode, granted, fastpath, count(*)
  from pg_locks
 group by locktype, virtualtransaction, pid, mode, granted, fastpath;

rollback;

\q

Re: delete on table with many partitions uses a lot of ram

[reg_pg_stefanz]

I must have missed this, I did not immediately realize there was a 
difference between select and delete

Thanks for the explanation and outlook.
Stefan

Re: TPC-DS queries

[reg_pg_stefanz]

Hi,

I think that the sql is not valid. Based on the order by documentation, 
a column label cannot be used in an expression.


from    https://www.postgresql.org/docs/11/queries-order.html
 > Note that an output column name has to stand alone, that is, it 
cannot be used in an expression.


Regards
s.

On 11.03.2019 06:30, Tatsuo Ishii wrote:

I played with TPC-DS and found some of them can't be executed because
of SQL errors and I am not sure why.

For example with query 36:

select
 sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin
,i_category
,i_class
,grouping(i_category)+grouping(i_class) as lochierarchy
,rank() over (
partition by grouping(i_category)+grouping(i_class),
case when grouping(i_class) = 0 then i_category end
order by sum(ss_net_profit)/sum(ss_ext_sales_price) asc) as 
rank_within_parent
  from
 store_sales
,date_dim   d1
,item
,store
  where
 d1.d_year = 2000
  and d1.d_date_sk = ss_sold_date_sk
  and i_item_sk  = ss_item_sk
  and s_store_sk  = ss_store_sk
  and s_state in ('TN','TN','TN','TN',
  'TN','TN','TN','TN')
  group by rollup(i_category,i_class)
  order by
lochierarchy desc
   ,case when lochierarchy = 0 then i_category end -- line 25 is here.
   ,rank_within_parent
   limit 100;
psql:query_0.sql:1935: ERROR:  column "lochierarchy" does not exist
LINE 25:   ,case when lochierarchy = 0 then i_category end

I have follwed the instruction here.
https://ankane.org/tpc-ds

PostgreSQL is master branch HEAD. For me, the SQL above looks to be
valid.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

Re: xmin and very high number of concurrent transactions

[reg_pg_stefanz]

I may have misunderstood the documentation or your question, but I had 
the understanding that xmin is not updated, but is only set on insert
(but yes, also for update, but updates are also inserts for Postgres as 
updates are executed as delete/insert)


from https://www.postgresql.org/docs/10/ddl-system-columns.html
> xmin
> The identity (transaction ID) of the inserting transaction for this 
row version. (A row version is an individual state of > row; each update 
of a row creates a new row version for the same logical row.)


therfore I assume, there are no actual updates of xmin values

Stefan

On 12.03.2019 20:19, Vijaykumar Jain wrote:

I was asked this question in one of my demos, and it was interesting one.

we update xmin for new inserts with the current txid.
now in a very high concurrent scenario where there are more than 2000
concurrent users trying to insert new data,
will updating xmin value be a bottleneck?

i know we should use pooling solutions to reduce concurrent
connections but given we have enough resources to take care of
spawning a new process for a new connection,

Regards,
Vijay

ERROR: no known snapshots

[reg_pg_stefanz]

Hi

it run into an issue with: ERROR:  no known snapshots

It seems to me whenever I use a toasted value in a loop in plpgsql code  
I get this error. Originally it happened in a procedure with a loop, 
without a setting of and explicit storage on the column, eg. extended.
I can reproduce the error with the simplified code below, when I force 
it it with external setting.
Is this a known issue, is there something wrong with the code or is 
there a workaround?


What I found so far:
- using set storage main and hoping 8K is enough seems to work so far
- without the commit it does not happen (originally this was on purpose 
as there was  more code in between, this is just a stripped down version)


Stefan


drop table if exists test1;
CREATE TABLE test1(i integer, txt text);
insert into test1 values (1, lpad('x', 3000));
insert into test1 values (2, lpad('x', 3000));

drop table if exists test2;
CREATE TABLE test2(i integer, txt text);
alter  table test2 alter column txt set storage external;
insert into test2 values (1, lpad('x', 3000));
insert into test2 values (2, lpad('x', 3000));

\echo  test1
DO $$
DECLARE
   r record;
   t text;
BEGIN
 FOR r in (SELECT txt FROM test1)
    LOOP
  t:=r.txt;
  COMMIT;
   END LOOP;
END;
$$;

\echo test2
DO $$
DECLARE
   r record;
   t text;
BEGIN
 FOR r in (SELECT txt FROM test2)
    LOOP
  t:=r.txt;
  COMMIT;
   END LOOP;
END;
$$;

\q

DROP TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
DROP TABLE
CREATE TABLE
ALTER TABLE
INSERT 0 1
INSERT 0 1
test1
DO
test2
psql:snapshot_error.sql:38: ERROR:  no known snapshots
CONTEXT:  PL/pgSQL function inline_code_block line 6 at FOR over SELECT rows

Re: ERROR: no known snapshots

[reg_pg_stefanz]

On 12.05.2021 05:42, Tom Lane wrote:

If you're in a position to apply the patch and see if it resolves
your real non-simplified case, that would be very helpful.

Also, this fix in principle will create a small performance
penalty for FOR-loops in non-atomic contexts such as DO loops.
It'd be interesting to know if the penalty is noticeable in
your usage.


Provided I understood your comment in the code, the slight performance 
impact should be due to the disabled prefetching. That should not be an 
issue, but I have not yet tested this.
What I have tested, I applied the patch to master and tested the actual 
code against the new build on a small testserver. This works for me.


However, I poked around a little bit, and this does not seem to solve 
all potential use cases, when I modify the simplified test by deferring 
the lookup to be done inside the loop as an extra lookup instead of 
doing it directly in the loop (whether that makes sense is another 
question) , then this still produces the error:



\echo test1
DO $$
DECLARE
   r record;
   t text;
BEGIN
 FOR r in (SELECT i FROM test1)
    LOOP
    select txt into t from test1 where i=r.i;
    COMMIT;
   END LOOP;
END;
$$;

\echo test2
DO $$
DECLARE
   r record;
   t text;
BEGIN
 FOR r in (SELECT i FROM test2)
    LOOP
  select txt into t from test2 where i=r.i;
  COMMIT;
   END LOOP;
END;
$$;

test1
DO
test2
psql:snapshot_error.sql:38: ERROR:  no known snapshots
CONTEXT:  PL/pgSQL function inline_code_block line 6 at FOR over SELECT rows