Re: update field in jsonb

[Oleg Bartunov]

On Thu, Nov 23, 2017 at 4:45 AM, support-tiger  wrote:
> is there a way to update a single field in jsonb without replacing the
> entire json document - couldn't find an example
>
> for example
>
> create table test (id primary key, data jsonb);
>
> insert into test ({"name":"bill", "age":29});
>
>  ?? update test   set data->age = 30


update test set data = jsonb_set(data, '{age}', '30'::jsonb);

>
>
> --
> Support Dept
> Tiger Nassau, Inc.
> www.tigernassau.com
> 406-624-9310
>
>
>

Re: update field in jsonb

[Ivan E. Panchenko]

23.11.2017 04:45, support-tiger пишет:
is there a way to update a single field in jsonb without replacing the 
entire json document - couldn't find an example


for example

create table test (id primary key, data jsonb);

insert into test ({"name":"bill", "age":29});

 ?? update test   set data->age = 30


When a record in PostgeSQL is UPDATEd, its new version is created. So 
such partial JSON update would be not more than some syntax sugar. That 
is why it is not yet implemented, though plans for that exist.


Now you have to do something like:

UPDATE test SET data = jsonb_set(data, ARRAY['age'], to_jsonb(30)) WHERE ..

update field in jsonb

[support-tiger]

is there a way to update a single field in jsonb without replacing the 
entire json document - couldn't find an example


for example

create table test (id primary key, data jsonb);

insert into test ({"name":"bill", "age":29});

 ?? update test   set data->age = 30


--
Support Dept
Tiger Nassau, Inc.
www.tigernassau.com
406-624-9310

Re: backends stuck in "startup"

[Tom Lane]

Justin Pryzby  writes:
> On Wed, Nov 22, 2017 at 07:43:50PM -0500, Tom Lane wrote:
>> My hypothesis about a missed memory barrier would imply that there's (at
>> least) one process that's waiting but is not in the lock's wait queue and

> Do I have to also check the wait queue to verify?  Give a hint/pointer please?

Andres probably knows more about this data structure than I do, but I
believe that the values in the LWLock's proclist_head field are indexes
into the PGProc array, and that the PGProc.lwWaitLink proclist_node fields
contain the fore and aft pointers in a doubly-linked list of waiting
processes.  But chasing through that by hand is going to be darn tedious
if there are a bunch of processes queued for the same lock.  In any case,
if the process is blocked right there and its lwWaiting field is not set,
that is sufficient proof of a bug IMO.  What is not quite proven yet is
why it failed to detect that it'd been woken.

I think really the most useful thing at this point is just to wait and
see if your SYSV-semaphore build exhibits the same problem or not.
If it does not, we can be pretty confident that *something* is wrong
with the POSIX-semaphore code, even if my current theory isn't it.

>> My theory suggests that any contended use of an LWLock is at risk,
>> in which case just running pgbench with about as many sessions as
>> you have in the live server ought to be able to trigger it.  However,
>> that doesn't really account for your having observed the problem
>> only during session startup,

> Remember, this issue breaks existing sessions, too.

Well, once one session is hung up, anything else that came along wanting
access to that same LWLock would also get stuck.  Since the lock in
question is a buffer partition lock controlling access to something like
1/128'th of the shared buffer pool, it would not take too long for every
active session to get stuck there, whether it were doing anything related
or not.

In any case, if you feel like trying the pgbench approach, I'd suggest
setting up a script to run a lot of relatively short runs rather than one
long one.  If there is something magic about the first blockage in a
session, that would help catch it.

> Am I right this won't help for lwlocks? ALTER SYSTEM SET log_lock_waits=yes

Nope, that's just for heavyweight locks.  LWLocks are lightweight
precisely because they don't have stuff like logging, timeouts,
or deadlock detection ...

regards, tom lane

Re: backends stuck in "startup"

[Justin Pryzby]

On Wed, Nov 22, 2017 at 07:43:50PM -0500, Tom Lane wrote:
> Justin Pryzby  writes:
> > For starters, I found that PID 27427 has:
> 
> > (gdb) p proc->lwWaiting
> > $1 = 0 '\000'
> > (gdb) p proc->lwWaitMode
> > $2 = 1 '\001'
> 
> To confirm, this is LWLockAcquire's "proc", equal to MyProc?
> If so, and if LWLockAcquire is blocked at PGSemaphoreLock,
> that sure seems like a smoking gun.

Right:

(gdb) bt
#0  0x003f6ee0d930 in ?? ()
#1  0x7f19f3400d38 in ?? ()
#2  0x006a4440 in PGSemaphoreLock (sema=0x7f19f3400d38) at pg_sema.c:310
#3  0x00714495 in LWLockAcquire (lock=0x7f19f3404f80, mode=LW_SHARED) 
at lwlock.c:1233
#4  0x006f75e5 in BufferAlloc (smgr=0x1f3c368, relpersistence=112 'p', 
forkNum=MAIN_FORKNUM, blockNum=1, mode=RBM_NORMAL, strategy=0x0, 
hit=0x7fff6ef1c5af "") at bufmgr.c:1012
#5  ReadBuffer_common (smgr=0x1f3c368, relpersistence=112 'p', 
forkNum=MAIN_FORKNUM, blockNum=1, mode=RBM_NORMAL, strategy=0x0, 
hit=0x7fff6ef1c5af "") at bufmgr.c:745
[...]

(gdb) up
#1  0x7f19f3400d38 in ?? ()
(gdb) up
#2  0x006a4440 in PGSemaphoreLock (sema=0x7f19f3400d38) at pg_sema.c:310
310 errStatus = sem_wait(PG_SEM_REF(sema));
(gdb) up
#3  0x00714495 in LWLockAcquire (lock=0x7f19f3404f80, mode=LW_SHARED) 
at lwlock.c:1233
1233PGSemaphoreLock(proc->sem);
(gdb) p proc->lwWaiting
$1 = 0 '\000'
(gdb) p proc->lwWaitMode
$2 = 1 '\001'
(gdb) p proc
$3 = (PGPROC *) 0x7f1a77dba500
(gdb) p MyProc
$4 = (PGPROC *) 0x7f1a77dba500
(gdb) p MyProc==proc
$5 = 1

I suppose one needs to show that this backend was stuck and not "just
happened to start 1ms before gdb connected", which I actually have:
postgres 27427 17665  0 16:57 ?00:00:00 postgres: indigo indigo 
207.241.169.248(37226) startup
...
postgres 30298 17665  0 17:01 ?00:00:00 postgres: main main 
207.241.169.100(43088) startup

..so that process was in "startup" 3-4 minutes after being forked.

> My hypothesis about a missed memory barrier would imply that there's (at
> least) one process that's waiting but is not in the lock's wait queue and

Do I have to also check the wait queue to verify?  Give a hint/pointer please?

> > Would you suggest how I can maximize the likelyhood/speed of triggering 
> > that ?
> > Five years ago, with a report of similar symptoms, you said "You need to 
> > hack
> > pgbench to suppress the single initialization connection it normally likes 
> > to
> > make, else the test degenerates to the one-incoming-connection case"
> > https://www.postgresql.org/message-id/8896.1337998337%40sss.pgh.pa.us
> 
> I don't think that case was related at all.
> 
> My theory suggests that any contended use of an LWLock is at risk,
> in which case just running pgbench with about as many sessions as
> you have in the live server ought to be able to trigger it.  However,
> that doesn't really account for your having observed the problem
> only during session startup,

Remember, this issue breaks existing sessions, too.

After it'd happened a couple times, I made sure to leave a session opened to
allow collecting diagnostics (and try to un-stick it), but when it recurred,
was unable to even SELECT * FROM pg_locks.

Am I right this won't help for lwlocks? ALTER SYSTEM SET log_lock_waits=yes

Justin

Re: query causes connection termination

[Tom Lane]

Tomas Vondra  writes:
> On 11/23/2017 01:25 AM, Neto pr wrote:
>> Anyone have any tips on why this occurs?

> Attach gdb to the backend, run the query and when it fails get us the
> backtrace. So something like

More details on that here:

https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend

regards, tom lane

Re: backends stuck in "startup"

[Tom Lane]

Justin Pryzby  writes:
> For starters, I found that PID 27427 has:

> (gdb) p proc->lwWaiting
> $1 = 0 '\000'
> (gdb) p proc->lwWaitMode
> $2 = 1 '\001'

To confirm, this is LWLockAcquire's "proc", equal to MyProc?
If so, and if LWLockAcquire is blocked at PGSemaphoreLock,
that sure seems like a smoking gun.

> Note: I've compiled locally PG 10.1 with PREFERRED_SEMAPHORES=SYSV to keep the
> service up (and to the degree that serves to verify that avoids the issue,
> great).

Good idea, I was going to suggest that.  It will be very interesting
to see if that makes the problem go away.

> Would you suggest how I can maximize the likelyhood/speed of triggering that ?
> Five years ago, with a report of similar symptoms, you said "You need to hack
> pgbench to suppress the single initialization connection it normally likes to
> make, else the test degenerates to the one-incoming-connection case"
> https://www.postgresql.org/message-id/8896.1337998337%40sss.pgh.pa.us

I don't think that case was related at all.

My theory suggests that any contended use of an LWLock is at risk,
in which case just running pgbench with about as many sessions as
you have in the live server ought to be able to trigger it.  However,
that doesn't really account for your having observed the problem
only during session startup, so there may be some other factor
involved.  I wonder if it only happens during the first wait for
an LWLock ... and if so, how could that be?

regards, tom lane

Re: query causes connection termination

[Tomas Vondra]

On 11/23/2017 01:25 AM, Neto pr wrote:
> Another fact is that when executing the query without the command
> EXPLAIN ANALYZE, the result is usually returned after a few minutes.
> I do not understand, because when using the EXPLAIN ANALYZE command the
> dbms closes the connection. 
> Anyone have any tips on why this occurs?
> 

Attach gdb to the backend, run the query and when it fails get us the
backtrace. So something like

1) select pg_backend_pid()

2) gdb -p $PID

3) run the EXPLAIN ANALYZE again

4) watch the gdb session, when it fails do 'bt'

You need to install debuginfo first, so that the backtrace makes sense.


regards

-- 
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: query causes connection termination

[Neto pr]

Another fact is that when executing the query without the command EXPLAIN
ANALYZE, the result is usually returned after a few minutes.
I do not understand, because when using the EXPLAIN ANALYZE command the
dbms closes the connection.
Anyone have any tips on why this occurs?

2017-11-22 21:19 GMT-03:00 Neto pr :

> Only complementing
> I use postgresql version 10.
> However the postgresql.conf file has standard settings.
> My server is a 2.8 GHz Xeon (4 core) and SSDs disc.
>
> 2017-11-22 21:12 GMT-03:00 Neto pr :
>
>> Dear all,
>> when executing a query, it causes the database to close the connection.
>> See the error reported by the SQL TOOL DBEAVER tool:
>>
>> -  DBEAVER SQL tool-
>> An I / O error occurred while sending to the backend.
>>java.io.EOFException:
>> 
>> ---
>>
>> I tried to execute the query in PSQL but the same thing happens. The
>> query is the 19 of the TPC-H Benchmark.
>>
>> ---PSQL Cliente Sql --
>> tpch40gnorssd=# EXPLAIN (ANALYZE)  select sum(l_extendedprice* (1 -
>> l_discount)) as revenue
>> tpch40gnorssd-#  fromlineitem, part
>>
>> tpch40gnorssd-#  where   (
>>
>> tpch40gnorssd(#  part.p_partkey = lineitem.l_partkey
>>
>> tpch40gnorssd(#  and part.p_brand = 'Brand#54'
>>
>> tpch40gnorssd(#  and part.p_container in ('SM CASE', 'SM
>> BOX', 'SM PACK', 'SM PKG')
>> tpch40gnorssd(#  and lineitem.l_quantity >= 4 and
>> lineitem.l_quantity <= 4 + 10
>> tpch40gnorssd(#  and part.p_size between 1 and 5
>>
>> tpch40gnorssd(#  and lineitem.l_shipmode in ('AIR', 'AIR
>> REG')
>> tpch40gnorssd(#  and lineitem.l_shipinstruct = 'DELIVER
>> IN PERSON'
>> tpch40gnorssd(#  )
>>
>> tpch40gnorssd-#  or
>>
>> tpch40gnorssd-#  (
>>
>> tpch40gnorssd(#  part.p_partkey = lineitem.l_partkey
>>
>> tpch40gnorssd(#  and part.p_brand = 'Brand#51'
>>
>> tpch40gnorssd(#  and part.p_container in ('MED BAG', 'MED
>> BOX', 'MED PKG', 'MED PACK')
>> tpch40gnorssd(#  and lineitem.l_quantity >= 11 and
>> lineitem.l_quantity <= 11 + 10
>> tpch40gnorssd(#  and part.p_size between 1 and 10
>>
>> tpch40gnorssd(#  and lineitem.l_shipmode in ('AIR', 'AIR
>> REG')
>> tpch40gnorssd(#  and lineitem.l_shipinstruct = 'DELIVER
>> IN PERSON'
>> tpch40gnorssd(#  )
>>
>> tpch40gnorssd-#  or
>>
>> tpch40gnorssd-#  (
>>
>> tpch40gnorssd(#  part.p_partkey = lineitem.l_partkey
>>
>> tpch40gnorssd(#  and part.p_brand = 'Brand#21'
>>
>> tpch40gnorssd(#  and part.p_container in ('LG CASE', 'LG
>> BOX', 'LG PACK', 'LG PKG')
>> tpch40gnorssd(#  and lineitem.l_quantity >= 28 and
>> lineitem.l_quantity <= 28 + 10
>> tpch40gnorssd(#  and part.p_size between 1 and 15
>>
>> tpch40gnorssd(#  and lineitem.l_shipmode in ('AIR', 'AIR
>> REG')
>>
>> tpch40gnorssd(#  and lineitem.l_shipinstruct = 'DELIVER
>> IN PERSON'
>>
>> tpch40gnorssd(#  );
>>
>>
>>
>> server closed the connection unexpectedly
>>
>>
>> This probably means the server terminated abnormally
>>
>>
>> before or while processing the request.
>>
>>
>> The connection to the server was lost. Attempting reset: Failed.
>>
>>
>> !>
>>
>>
>> !>
>> 
>>
>> However, when executing an Explain query, no error is reported.
>>
>>
>> - EXPLAIN ONLY 
>>
>> Finalize Aggregate  (cost=280394.81..280394.82 rows=1 width=32)
>>->  Gather  (cost=280394.59..280394.80 rows=2 width=32)
>>  Workers Planned: 2
>>  ->  Partial Aggregate  (cost=279394.59..279394.60 rows=1
>> width=32)
>>->  Nested Loop  (cost=29935.44..279381.95 rows=1685
>> width=12)
>>  ->  Parallel Bitmap Heap Scan on part
>> (cost=29934.87..48103.87 rows=7853 width=30)
>>Recheck Cond: (((p_brand = 'Brand#54'::bpchar)
>> AND (p_size <= 5) AND (p_size >= 1) AND (p_container = ANY ('{"SM CASE","SM
>> BOX","SM PACK","SM PKG"}'::bpchar[]))) OR ((p_brand =
>>  'Brand#51'::bpchar) AND (p_size <= 10) AND (p_size >= 1) AND
>> (p_container = ANY ('{"MED BAG","MED BOX","MED PKG","MED
>> PACK"}'::bpchar[]))) OR ((p_brand = 'Brand#21'::bpchar) AND (p_size <= 15)
>> AND (p_si
>> ze >= 1) AND (p_container = ANY ('{"LG CASE","LG BOX","LG PACK","LG
>> PKG"}'::bpchar[]
>>->  BitmapOr  (cost=29934.87..29934.87
>> rows=18861 width=0)
>>  ->  BitmapAnd  (cost=9559.76..9559.76
>> rows=3140 width=0)
>>

Re: query causes connection termination

[Neto pr]

Only complementing
I use postgresql version 10.
However the postgresql.conf file has standard settings.
My server is a 2.8 GHz Xeon (4 core) and SSDs disc.

2017-11-22 21:12 GMT-03:00 Neto pr :

> Dear all,
> when executing a query, it causes the database to close the connection.
> See the error reported by the SQL TOOL DBEAVER tool:
>
> -  DBEAVER SQL tool-
> An I / O error occurred while sending to the backend.
>java.io.EOFException:
> 
> ---
>
> I tried to execute the query in PSQL but the same thing happens. The query
> is the 19 of the TPC-H Benchmark.
>
> ---PSQL Cliente Sql --
> tpch40gnorssd=# EXPLAIN (ANALYZE)  select sum(l_extendedprice* (1 -
> l_discount)) as revenue
> tpch40gnorssd-#  fromlineitem, part
>
> tpch40gnorssd-#  where   (
>
> tpch40gnorssd(#  part.p_partkey = lineitem.l_partkey
>
> tpch40gnorssd(#  and part.p_brand = 'Brand#54'
>
> tpch40gnorssd(#  and part.p_container in ('SM CASE', 'SM
> BOX', 'SM PACK', 'SM PKG')
> tpch40gnorssd(#  and lineitem.l_quantity >= 4 and
> lineitem.l_quantity <= 4 + 10
> tpch40gnorssd(#  and part.p_size between 1 and 5
>
> tpch40gnorssd(#  and lineitem.l_shipmode in ('AIR', 'AIR
> REG')
> tpch40gnorssd(#  and lineitem.l_shipinstruct = 'DELIVER IN
> PERSON'
> tpch40gnorssd(#  )
>
> tpch40gnorssd-#  or
>
> tpch40gnorssd-#  (
>
> tpch40gnorssd(#  part.p_partkey = lineitem.l_partkey
>
> tpch40gnorssd(#  and part.p_brand = 'Brand#51'
>
> tpch40gnorssd(#  and part.p_container in ('MED BAG', 'MED
> BOX', 'MED PKG', 'MED PACK')
> tpch40gnorssd(#  and lineitem.l_quantity >= 11 and
> lineitem.l_quantity <= 11 + 10
> tpch40gnorssd(#  and part.p_size between 1 and 10
>
> tpch40gnorssd(#  and lineitem.l_shipmode in ('AIR', 'AIR
> REG')
> tpch40gnorssd(#  and lineitem.l_shipinstruct = 'DELIVER IN
> PERSON'
> tpch40gnorssd(#  )
>
> tpch40gnorssd-#  or
>
> tpch40gnorssd-#  (
>
> tpch40gnorssd(#  part.p_partkey = lineitem.l_partkey
>
> tpch40gnorssd(#  and part.p_brand = 'Brand#21'
>
> tpch40gnorssd(#  and part.p_container in ('LG CASE', 'LG
> BOX', 'LG PACK', 'LG PKG')
> tpch40gnorssd(#  and lineitem.l_quantity >= 28 and
> lineitem.l_quantity <= 28 + 10
> tpch40gnorssd(#  and part.p_size between 1 and 15
>
> tpch40gnorssd(#  and lineitem.l_shipmode in ('AIR', 'AIR
> REG')
>
> tpch40gnorssd(#  and lineitem.l_shipinstruct = 'DELIVER IN
> PERSON'
>
> tpch40gnorssd(#  );
>
>
>
> server closed the connection unexpectedly
>
>
> This probably means the server terminated abnormally
>
>
> before or while processing the request.
>
>
> The connection to the server was lost. Attempting reset: Failed.
>
>
> !>
>
>
> !>
> 
>
> However, when executing an Explain query, no error is reported.
>
>
> - EXPLAIN ONLY 
>
> Finalize Aggregate  (cost=280394.81..280394.82 rows=1 width=32)
>->  Gather  (cost=280394.59..280394.80 rows=2 width=32)
>  Workers Planned: 2
>  ->  Partial Aggregate  (cost=279394.59..279394.60 rows=1 width=32)
>->  Nested Loop  (cost=29935.44..279381.95 rows=1685
> width=12)
>  ->  Parallel Bitmap Heap Scan on part
> (cost=29934.87..48103.87 rows=7853 width=30)
>Recheck Cond: (((p_brand = 'Brand#54'::bpchar)
> AND (p_size <= 5) AND (p_size >= 1) AND (p_container = ANY ('{"SM CASE","SM
> BOX","SM PACK","SM PKG"}'::bpchar[]))) OR ((p_brand =
>  'Brand#51'::bpchar) AND (p_size <= 10) AND (p_size >= 1) AND (p_container
> = ANY ('{"MED BAG","MED BOX","MED PKG","MED PACK"}'::bpchar[]))) OR
> ((p_brand = 'Brand#21'::bpchar) AND (p_size <= 15) AND (p_si
> ze >= 1) AND (p_container = ANY ('{"LG CASE","LG BOX","LG PACK","LG
> PKG"}'::bpchar[]
>->  BitmapOr  (cost=29934.87..29934.87
> rows=18861 width=0)
>  ->  BitmapAnd  (cost=9559.76..9559.76
> rows=3140 width=0)
>->  Bitmap Index Scan on
> idx_p_brand_p_size  (cost=0.00..508.37 rows=31035 width=0)
>  Index Cond: ((p_brand =
> 'Brand#54'::bpchar) AND (p_size <= 5) AND (p_size >= 1))
>->  Bitmap Index Scan on
> idx_p_containerpart000  (cost=0.00..9041.72 rows=809333 width=0)
>  Index Cond: (p_container =
> ANY ('{"SM CASE","SM 

query causes connection termination

[Neto pr]

Dear all,
when executing a query, it causes the database to close the connection.
See the error reported by the SQL TOOL DBEAVER tool:

-  DBEAVER SQL tool-
An I / O error occurred while sending to the backend.
   java.io.EOFException:
---

I tried to execute the query in PSQL but the same thing happens. The query
is the 19 of the TPC-H Benchmark.

---PSQL Cliente Sql --
tpch40gnorssd=# EXPLAIN (ANALYZE)  select sum(l_extendedprice* (1 -
l_discount)) as revenue
tpch40gnorssd-#  fromlineitem, part

tpch40gnorssd-#  where   (

tpch40gnorssd(#  part.p_partkey = lineitem.l_partkey

tpch40gnorssd(#  and part.p_brand = 'Brand#54'

tpch40gnorssd(#  and part.p_container in ('SM CASE', 'SM
BOX', 'SM PACK', 'SM PKG')
tpch40gnorssd(#  and lineitem.l_quantity >= 4 and
lineitem.l_quantity <= 4 + 10
tpch40gnorssd(#  and part.p_size between 1 and 5

tpch40gnorssd(#  and lineitem.l_shipmode in ('AIR', 'AIR
REG')
tpch40gnorssd(#  and lineitem.l_shipinstruct = 'DELIVER IN
PERSON'
tpch40gnorssd(#  )

tpch40gnorssd-#  or

tpch40gnorssd-#  (

tpch40gnorssd(#  part.p_partkey = lineitem.l_partkey

tpch40gnorssd(#  and part.p_brand = 'Brand#51'

tpch40gnorssd(#  and part.p_container in ('MED BAG', 'MED
BOX', 'MED PKG', 'MED PACK')
tpch40gnorssd(#  and lineitem.l_quantity >= 11 and
lineitem.l_quantity <= 11 + 10
tpch40gnorssd(#  and part.p_size between 1 and 10

tpch40gnorssd(#  and lineitem.l_shipmode in ('AIR', 'AIR
REG')
tpch40gnorssd(#  and lineitem.l_shipinstruct = 'DELIVER IN
PERSON'
tpch40gnorssd(#  )

tpch40gnorssd-#  or

tpch40gnorssd-#  (

tpch40gnorssd(#  part.p_partkey = lineitem.l_partkey

tpch40gnorssd(#  and part.p_brand = 'Brand#21'

tpch40gnorssd(#  and part.p_container in ('LG CASE', 'LG
BOX', 'LG PACK', 'LG PKG')
tpch40gnorssd(#  and lineitem.l_quantity >= 28 and
lineitem.l_quantity <= 28 + 10
tpch40gnorssd(#  and part.p_size between 1 and 15

tpch40gnorssd(#  and lineitem.l_shipmode in ('AIR', 'AIR
REG')

tpch40gnorssd(#  and lineitem.l_shipinstruct = 'DELIVER IN
PERSON'

tpch40gnorssd(#  );



server closed the connection unexpectedly


This probably means the server terminated abnormally


before or while processing the request.


The connection to the server was lost. Attempting reset: Failed.


!>


!>


However, when executing an Explain query, no error is reported.


- EXPLAIN ONLY 

Finalize Aggregate  (cost=280394.81..280394.82 rows=1 width=32)
   ->  Gather  (cost=280394.59..280394.80 rows=2 width=32)
 Workers Planned: 2
 ->  Partial Aggregate  (cost=279394.59..279394.60 rows=1 width=32)
   ->  Nested Loop  (cost=29935.44..279381.95 rows=1685
width=12)
 ->  Parallel Bitmap Heap Scan on part
(cost=29934.87..48103.87 rows=7853 width=30)
   Recheck Cond: (((p_brand = 'Brand#54'::bpchar)
AND (p_size <= 5) AND (p_size >= 1) AND (p_container = ANY ('{"SM CASE","SM
BOX","SM PACK","SM PKG"}'::bpchar[]))) OR ((p_brand =
 'Brand#51'::bpchar) AND (p_size <= 10) AND (p_size >= 1) AND (p_container
= ANY ('{"MED BAG","MED BOX","MED PKG","MED PACK"}'::bpchar[]))) OR
((p_brand = 'Brand#21'::bpchar) AND (p_size <= 15) AND (p_si
ze >= 1) AND (p_container = ANY ('{"LG CASE","LG BOX","LG PACK","LG
PKG"}'::bpchar[]
   ->  BitmapOr  (cost=29934.87..29934.87
rows=18861 width=0)
 ->  BitmapAnd  (cost=9559.76..9559.76
rows=3140 width=0)
   ->  Bitmap Index Scan on
idx_p_brand_p_size  (cost=0.00..508.37 rows=31035 width=0)
 Index Cond: ((p_brand =
'Brand#54'::bpchar) AND (p_size <= 5) AND (p_size >= 1))
   ->  Bitmap Index Scan on
idx_p_containerpart000  (cost=0.00..9041.72 rows=809333 width=0)
 Index Cond: (p_container = ANY
('{"SM CASE","SM BOX","SM PACK","SM PKG"}'::bpchar[]))
 ->  BitmapAnd  (cost=9837.67..9837.67
rows=6022 width=0)
   ->  Bitmap Index Scan on
idx_p_brand_p_size  (cost=0.00..997.27 rows=60947 width=0)
 Index Cond: ((p_brand =
'Brand#51'::bpchar) AND (p_size <= 10) AND (p_size >= 1))
   ->  Bitmap Index Scan on

Re: backends stuck in "startup"

[Justin Pryzby]

On Wed, Nov 22, 2017 at 01:27:12PM -0500, Tom Lane wrote:
> Justin Pryzby  writes:
> > On Tue, Nov 21, 2017 at 03:40:27PM -0800, Andres Freund wrote:
> >> Could you try stracing next time?
> 
> > I straced all the "startup" PIDs, which were all in futex, without 
> > exception:
> 
> If you've got debug symbols installed, could you investigate the states
> of the LWLocks the processes are stuck on?
> 
> My hypothesis about a missed memory barrier would imply that there's (at
> least) one process that's waiting but is not in the lock's wait queue and
> has MyProc->lwWaiting == false, while the rest are in the wait queue and
> have MyProc->lwWaiting == true.  Actually chasing through the list
> pointers would be slightly tedious, but checking MyProc->lwWaiting,
> and maybe MyProc->lwWaitMode, in each process shouldn't be too hard.

> Also verify that they're all waiting for the same LWLock (by address).

I believe my ~40 cores are actually for backends from two separate instances of
this issue on the VM, as evidenced by different argv pointers.

And for each instance, I have cores for only a fraction of the backends
(max_connections=400).

For starters, I found that PID 27427 has:

(gdb) p proc->lwWaiting
$1 = 0 '\000'
(gdb) p proc->lwWaitMode
$2 = 1 '\001'

..where all the others have lwWaiting=1

For #27427:
(gdb) p *lock
$27 = {tranche = 59, state = {value = 1627389952}, waiters = {head = 147, tail 
= 308}}

(gdb) info locals
mustwait = 1 '\001'
proc = 0x7f1a77dba500
result = 1 '\001'
extraWaits = 0
__func__ = "LWLockAcquire"

And at this point I have to ask for help how to finish traversing these
structures.  I could upload cores for someone (I don't think there's anything
too private) but so far I have 16GB gz compressed cores.

Note: I've compiled locally PG 10.1 with PREFERRED_SEMAPHORES=SYSV to keep the
service up (and to the degree that serves to verify that avoids the issue,
great).

But I could start an instance running pgbench to try to trigger on this VM,
with smaller shared_buffers and backends/clients to allow full cores of every
backend (I don't think I'll be able to dump all 400 cores each up to 2GB from
the production instance).

Would you suggest how I can maximize the likelyhood/speed of triggering that ?
Five years ago, with a report of similar symptoms, you said "You need to hack
pgbench to suppress the single initialization connection it normally likes to
make, else the test degenerates to the one-incoming-connection case"
https://www.postgresql.org/message-id/8896.1337998337%40sss.pgh.pa.us

..but, pgbench --connect seems to do what's needed(?)  (I see that dates back
to 2001, having been added at ba708ea3).

(I don't know there's any suggestion or reason to be believe the bug is
specific to connection/startup phase, or that it's a necessary or sufficient to
hit the bug, but it's at least known to be impacted and all I have to go on for
now).

Justin

Re: migrations (was Re: To all who wish to unsubscribe)

[Carl Karsten]

I would like to remind everyone that
a) we are all friends,
and b) we don't need to reply to everything.

Now for some humour

https://xkcd.com/386/ "someone is wrong on the internet"

Tail of Banana talk, just before Get me off your ... very strong language
https://youtu.be/eC9rmsI7dnw?t=1m32s
(I make videos of presentations, that is a friend giving me something to
test new video mixing software.)

And thank you list admins for doing your admin things, both technical and
soft.



On Wed, Nov 22, 2017 at 8:43 AM, Tom Lane  wrote:

> Geoff Winkless  writes:
> > On 22 November 2017 at 14:19, Vick Khera  wrote:
> >> This sounds to me like you're going to run afoul of CAN-SPAM
> unsubscribe rules. You should re-think this workflow.
>
> > CAN-SPAM only applies to commercial email, "the primary purpose of
> > which is the commercial advertisement or promotion of a commercial
> > product or service”. This is a discussion mailing list and as such
> > does not come under the remit of CAN-SPAM.
>
> Even if it did, you do not need a community account to unsubscribe,
> only if you want to adjust your subscription in some other way.
>
> regards, tom lane
>
>


-- 
Carl K

Re: duplicate primary key

[Alexander Pyhalov]

On 11/22/17 07:24 PM, Magnus Hagander wrote:


What is your postgres version, and what's the "version history" of upgrades
from it (talking pg_upgrade upgrades, not dump/reload upgrades). This might
be fallout from old bugs thaat have been known to cause this type of
problem.



It's PostgreSQL 9.5.10 (64bit), running on Ubuntu 16.04.
Last major update was done as full dump/restore.

--
Best regards,
Alexander Pyhalov,
system administrator of Southern Federal University IT department

Re: duplicate primary key

[Magnus Hagander]

On Wed, Nov 22, 2017 at 12:05 PM, Alexander Pyhalov  wrote:

> Hello.
>
> I'm a bit shocked. During import/export of our database we've found a
> duplicate primary key.
>
> # \d player
>
> Table "public.player"
>Column   |Type |
>  Modifiers
> +-+-
> 
> ---
>  id | integer | not null default
> nextval('player_id_seq'::regclass)
> ...
> Indexes:
> "pk_id" PRIMARY KEY, btree (id)
> ...
>
> # select * from pg_indexes where indexname='pk_id';
>  schemaname | tablename | indexname | tablespace |  indexdef
> +---+---++--
> 
>  public | player| pk_id || CREATE UNIQUE INDEX
> pk_id ON player USING btree (id)
>
> # select * from pg_constraint where conname='pk_id';
> -[ RECORD 1 ]-+--
> conname   | pk_id
> connamespace  | 2200
> contype   | p
> condeferrable | f
> condeferred   | f
> convalidated  | t
> conrelid  | 18319
> contypid  | 0
> conindid  | 18640
> confrelid | 0
> confupdtype   |
> confdeltype   |
> confmatchtype |
> conislocal| t
> coninhcount   | 0
> connoinherit  | t
> conkey| {1}
> confkey   |
> conpfeqop |
> conppeqop |
> conffeqop |
> conexclop |
> conbin|
> consrc|
>
> # select count(*) from player where id=14875;
> -[ RECORD 1 ]
> count | 2
>
> The records are identical, besides ctid,xmin,xmax
>
> # select tableoid,ctid,id,xmin,xmax from player where id=14875;
>  tableoid | ctid |id |xmin|xmax
> --+--+---++
> 18319 | (9982129,2)  | 14875 | 3149449600 | 3152681810
> 18319 | (9976870,49) | 14875 | 3149448769 | 3152328995
>
>
>
> I don't understand how this could have happened
>
>
What is your postgres version, and what's the "version history" of upgrades
from it (talking pg_upgrade upgrades, not dump/reload upgrades). This might
be fallout from old bugs thaat have been known to cause this type of
problem.



-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: Set role dynamically from proc

[David G. Johnston]

On Wed, Nov 22, 2017 at 7:52 AM, Durumdara  wrote:

> Now the "set role" uses the "variable name", and not the "value of the
> variable".
>
> This is what I don't like in this lang. I need to write a special variable
> name to "force" to use it, and not other thing.
> I don't know how to force the system to use my variable value, and not my
> variable name in the routines.
> For example $var$, or , or [var], etc.
>
> I can generate "set role" with string concat, but in PGAdmin this script
> would be good.
>
>
​There are three kinds of "words" in an SQL command: keywords, identifiers,
​values.

SET ROLE TO davidj;

SET := keyword
ROLE := keyword
TO := keyword
davidj := *identifier*

In pl/pgsql variables can only replace values, not identifiers or
keywords.  Since the variable in this case holds an identifier you must use
dynamic SQL to execute the statement you want.

not tested but basically:

EXECUTE format('SET ROLE %I', variablename); --(that's a percent-eye)

More generally when using "PREPARE" at the SQL-level only values can be
replaced with placeholders ($1, $2, etc...).  All pl/pgsql is doing when
you use a variable is writing out a PREPARE variation of your command and
the executing it with the variable values as parameter values.

Identifiers are any words that the system is going to lookup in a catalog
(I may be over-simplifying a bit, and the converse, values are not resolved
in a catalog, is generally but possibly not always, true).  Identifiers can
always be double-quoted to avoid their case-folding behavior.

David J.

[GENERAL] postgres_fdw & could not receive data from client: Connection reset by peer

[Adam Brusselback]

Hey all, first off...
Postgres version: 10.1
OS: Debian 9.0

So I have a database called: authentication
It stores my user table for my application.  I have it separated from
the main database of my application to allow the same account to be
used by multiple instances of my application.

>From a connection to my main database, I need to be able to query this
table as if it existed in my main database, so I set up a postgres_fdw
connection to it.  This works well, and I have no issues with the
setup functionally.  I can have multiple instances of my main
application database (test, dev, prod), all pointed to the same
"authentication" database, and everything works. My users can keep
logging into the application with the same credentials no matter which
environment they go into.

The only thing I have seen, is pretty much any query which references
the foreign user table (and connects to the auth db's user table) will
put a line in my log file like so:

2017-11-22 14:27:09.846 UTC [17388] authentication@authentication LOG:
 could not receive data from client: Connection reset by peer

I was wondering if this was normal behavior, or if i'm doing something
wrong to cause this?  It happens no matter if my queries come from
pgadmin, psql, or jdbc.

Any info would be greatly appreciated.
Thanks,
-Adam

Re: Set role dynamically from proc

[Durumdara]

Hello!

I haven't got your mail, I see your answer only in the thread of web
mailing list.

> It seems that the user does not exist:

The problem is based on that I want to set the role to the database owner
from script.
My team members many times logged as "postgres" or diff user, and forget to
set the role to DB owner.
I thought I can avoid the problems with changing the actual role to real
owner before the changes.

Now the "set role" uses the "variable name", and not the "value of the
variable".

This is what I don't like in this lang. I need to write a special variable
name to "force" to use it, and not other thing.
I don't know how to force the system to use my variable value, and not my
variable name in the routines.
For example $var$, or , or [var], etc.

I can generate "set role" with string concat, but in PGAdmin this script
would be good.

Thank you!
   dd





2017-11-22 14:55 GMT+01:00 Durumdara :

> Hello!
>
> May you know the way how to set role dynamically.
>
> DO
> $$
> DECLARE act_dbowner varchar(100);
> BEGIN
>
> SELECT u.usename into act_dbowner FROM pg_database d
> JOIN pg_user u ON (d.datdba = u.usesysid)
> WHERE d.datname = (SELECT current_database());
> raise notice 'DB owner: %', act_dbowner;
>
> *set role to act_dbowner; --  THIS LINE*
> END
> $$;
>
> -
>
> ERROR:  role "act_dbowner" does not exist
> CONTEXT:  SQL statement "set role to act_dbowner"
> PL/pgSQL function inline_code_block line 10 at SQL statement
>
>
> I try to use $act_dbowner, but it have no effect.
>
> Thank you for your help!
>
> Best wishes
>dd
>

Re: migrations (was Re: To all who wish to unsubscribe)

[Tom Lane]

Geoff Winkless  writes:
> On 22 November 2017 at 14:19, Vick Khera  wrote:
>> This sounds to me like you're going to run afoul of CAN-SPAM unsubscribe 
>> rules. You should re-think this workflow.

> CAN-SPAM only applies to commercial email, "the primary purpose of
> which is the commercial advertisement or promotion of a commercial
> product or service”. This is a discussion mailing list and as such
> does not come under the remit of CAN-SPAM.

Even if it did, you do not need a community account to unsubscribe,
only if you want to adjust your subscription in some other way.

regards, tom lane

Re: migrations (was Re: To all who wish to unsubscribe)

[Stephen Frost]

Vick,

* Vick Khera (vi...@khera.org) wrote:
> On Wed, Nov 22, 2017 at 9:07 AM, Stephen Frost  wrote:
> > * Mike Sofen (mso...@runbox.com) wrote:
> > > Confirmed, I did not get a reset email either.  Also, I had subscribed
> > to the digest version and now I get individual emails - which is why I
> > needed to login, but my password no longer worked.  Sigh.  Mike
> >
> > I've replied to Mike directly off-list, but wanted to let others know
> > who might be concerned about the reset link- it *does* work, but only if
> > you have an account already in the system.
> >
> > It's possible to be subscribed to the PostgreSQL mailing lists without
> > having a community account.  If you find that you don't get an email
> > after going through the 'reset password' link, try creating a new
> > account instead.
> 
> This sounds to me like you're going to run afoul of CAN-SPAM unsubscribe
> rules. You should re-think this workflow.

To be clear- you do *NOT* need an account to unsubscribe, you need an
account to be able to manage your subscriptions and to change global
settings for your subscriptions.

As mentioned many times already, you can unsubscribe, without an
account, by following the link in each and every email that is sent by
the list server in the List-Unsubscribe header, as hundreds have already
done.  Further, an email can be sent to
pgsql-general-unsubscr...@postgresql.org to request unsubscription
without having an account.

And, lastly, if you wish to have any further discussion regarding the
mailing lists, please contact me directly, I believe we've gone over
this quite enough already on this list and it's not really what this
list is for.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: migrations (was Re: To all who wish to unsubscribe)

[Geoff Winkless]

On 22 November 2017 at 14:19, Vick Khera  wrote:
>
> This sounds to me like you're going to run afoul of CAN-SPAM unsubscribe 
> rules. You should re-think this workflow.


CAN-SPAM only applies to commercial email, "the primary purpose of
which is the commercial advertisement or promotion of a commercial
product or service”. This is a discussion mailing list and as such
does not come under the remit of CAN-SPAM.

Geoff

Re: migrations (was Re: To all who wish to unsubscribe)

[Vick Khera]

On Wed, Nov 22, 2017 at 9:07 AM, Stephen Frost  wrote:

> Greetings everyone,
>
> * Mike Sofen (mso...@runbox.com) wrote:
> > Confirmed, I did not get a reset email either.  Also, I had subscribed
> to the digest version and now I get individual emails - which is why I
> needed to login, but my password no longer worked.  Sigh.  Mike
>
> I've replied to Mike directly off-list, but wanted to let others know
> who might be concerned about the reset link- it *does* work, but only if
> you have an account already in the system.
>
> It's possible to be subscribed to the PostgreSQL mailing lists without
> having a community account.  If you find that you don't get an email
> after going through the 'reset password' link, try creating a new
> account instead.
>

This sounds to me like you're going to run afoul of CAN-SPAM unsubscribe
rules. You should re-think this workflow.

RE: Set role dynamically from proc

[Charles Clavadetscher]

Hello

 

From: Durumdara [mailto:durumd...@gmail.com] 
Sent: Mittwoch, 22. November 2017 14:56
To: pgsql-gene...@postgresql.org
Subject: Set role dynamically from proc

 

Hello!

 

May you know the way how to set role dynamically.

 

DO 

$$

DECLARE act_dbowner varchar(100);

BEGIN

 

SELECT u.usename into act_dbowner FROM pg_database d

JOIN pg_user u ON (d.datdba = u.usesysid)

WHERE d.datname = (SELECT current_database());

raise notice 'DB owner: %', act_dbowner;

 

set role to act_dbowner; --  THIS LINE



END

$$;

 

-

 

ERROR:  role "act_dbowner" does not exist

CONTEXT:  SQL statement "set role to act_dbowner"

PL/pgSQL function inline_code_block line 10 at SQL statement

 

I try to use $act_dbowner, but it have no effect.

 

It seems that the user does not exist:

 

CREATE OR REPLACE FUNCTION set_role()

RETURNS VOID

AS $$

BEGIN

  RAISE NOTICE 'CURRENT_USER: %', (select current_user);

  SET ROLE blabla;

  RAISE NOTICE 'CURRENT_USER: %', (select current_user);

END;

$$ LANGUAGE plpgsql;

 

db=> select * from set_role();

NOTICE:  CURRENT_USER: kofadmin

ERROR:  role "blabla" does not exist

CONTEXT:  SQL statement "SET ROLE blabla"

PL/pgSQL function set_role() line 4 at SQL statement

 

db=> CREATE ROLE blabla;

CREATE ROLE

 

db=> select * from set_role();

NOTICE:  CURRENT_USER: kofadmin

ERROR:  permission denied to set role "blabla"

CONTEXT:  SQL statement "SET ROLE blabla"

PL/pgSQL function set_role() line 4 at SQL statement

 

db=> GRANT blabla TO kofadmin;

GRANT ROLE

 

Now it works:

 

db=> select * from set_role();

NOTICE:  CURRENT_USER: kofadmin

NOTICE:  CURRENT_USER: blabla

[...]

 

Regards

Charles

 

Thank you for your help!

 

Best wishes

   dd

Re: migrations (was Re: To all who wish to unsubscribe)

[Stephen Frost]

Greetings everyone,

* Mike Sofen (mso...@runbox.com) wrote:
> Confirmed, I did not get a reset email either.  Also, I had subscribed to the 
> digest version and now I get individual emails - which is why I needed to 
> login, but my password no longer worked.  Sigh.  Mike

I've replied to Mike directly off-list, but wanted to let others know
who might be concerned about the reset link- it *does* work, but only if
you have an account already in the system.

It's possible to be subscribed to the PostgreSQL mailing lists without
having a community account.  If you find that you don't get an email
after going through the 'reset password' link, try creating a new
account instead.

Once you have an account, you can go over to the
https://lists.postgresql.org/manage system and see any lists which that
email address is subscribed to and, further, add other email addresses
that you have control over (there will be an email sent to each address
with a link for you to follow to confirm that you do have control over
it) and then you'll be able to see the lists those email addresses are
associated with.

Hope that helps, as always, please feel free to contact me directly,
off-list, with any further questions or concerns regarding the
PostgreSQL mailing lists or your subscriptions or accounts.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: Reset Sequence number

[Thomas Kellerer]

Brahmam Eswar schrieb am 22.11.2017 um 10:36:
> 
> we are in process of migrating to postgres and need to reset the
> sequence number with highest value of table key. I want to make it
> procedural to do that as mentioned below, But it's throwing an error

> DO $$
>  DECLARE
>  SEQ BIGINT;
>  BEGIN 
>  
>  SEQ:=(SELECT MAX(ID) FROM TABLE_1);
>  ALTER SEQUENCE TABLE_1_SEQ  RESTART WITH SEQ;
>   
>  END$$; 
>  
> Error : syntax error at or near "SEQ"

No need for a PL/pgSQL block.

You can do that with a plain SQL statement using setval():

   select setval('table_1_seq', (select max(id) from table1));

Set role dynamically from proc

[Durumdara]

Hello!

May you know the way how to set role dynamically.

DO
$$
DECLARE act_dbowner varchar(100);
BEGIN

SELECT u.usename into act_dbowner FROM pg_database d
JOIN pg_user u ON (d.datdba = u.usesysid)
WHERE d.datname = (SELECT current_database());
raise notice 'DB owner: %', act_dbowner;

*set role to act_dbowner; --  THIS LINE*
END
$$;

-

ERROR:  role "act_dbowner" does not exist
CONTEXT:  SQL statement "set role to act_dbowner"
PL/pgSQL function inline_code_block line 10 at SQL statement


I try to use $act_dbowner, but it have no effect.

Thank you for your help!

Best wishes
   dd

Re: duplicate primary key

[Thomas Markus]

Am 22.11.17 um 12:05 schrieb Alexander Pyhalov:

Hello.

I'm a bit shocked. During import/export of our database we've found a 
duplicate primary key.


# \d player

Table "public.player"
   Column   |    Type | 
    Modifiers
+-+ 

 id | integer | not null default 
nextval('player_id_seq'::regclass)

...
Indexes:
    "pk_id" PRIMARY KEY, btree (id)
...

# select * from pg_indexes where indexname='pk_id';
 schemaname | tablename | indexname | tablespace |  indexdef
+---+---++-- 

 public | player    | pk_id |    | CREATE UNIQUE INDEX 
pk_id ON player USING btree (id)


# select * from pg_constraint where conname='pk_id';
-[ RECORD 1 ]-+--
conname   | pk_id
connamespace  | 2200
contype   | p
condeferrable | f
condeferred   | f
convalidated  | t
conrelid  | 18319
contypid  | 0
conindid  | 18640
confrelid | 0
confupdtype   |
confdeltype   |
confmatchtype |
conislocal    | t
coninhcount   | 0
connoinherit  | t
conkey    | {1}
confkey   |
conpfeqop |
conppeqop |
conffeqop |
conexclop |
conbin    |
consrc    |

# select count(*) from player where id=14875;
-[ RECORD 1 ]
count | 2

The records are identical, besides ctid,xmin,xmax

# select tableoid,ctid,id,xmin,xmax from player where id=14875;
 tableoid | ctid |    id |    xmin    |    xmax
--+--+---++
    18319 | (9982129,2)  | 14875 | 3149449600 | 3152681810
    18319 | (9976870,49) | 14875 | 3149448769 | 3152328995



I don't understand how this could have happened


Hi Alex,

we got this with a broken index. Fix data and rebuild them. And check 
your system/storage


Thomas

Re: backends stuck in "startup"

[Justin Pryzby]

On Tue, Nov 21, 2017 at 03:40:27PM -0800, Andres Freund wrote:
> Hi,
> 
> On 2017-11-21 17:09:26 -0600, Justin Pryzby wrote:
> > I'm sorry to report this previously reported problem is happening again,
> > starting shortly after pg_upgrading a customer to PG10.1 from 9.6.5.
> > 
> > As $subject: backends are stuck in startup for minutes at a time.  I didn't
> > strace this time, but I believe last time I saw one was waiting in a futex.
> 
> >  - I'm planning do disable sync seqscan for the night (although as I 
> > understand
> >that may not matter (?))
> 
> I doubt it matters here.

The system still got wedged/stuck after a few hours even with:

synchronize_seqscans=off; and use of pg_buffercache disabled (but extension not
actually dropped), and our maintenance (vacuum and reindex) scripts disabled
(these normally only run at night, so at least REINDEX script wasn't involved
with several earlier incidents of this issue; but, I was still running the
vacuum ANALYZE script post-upgrade).

Justin

duplicate primary key

[Alexander Pyhalov]

Hello.

I'm a bit shocked. During import/export of our database we've found a 
duplicate primary key.


# \d player

Table "public.player"
   Column   |Type | 
Modifiers 


+-+
 id | integer | not null default 
nextval('player_id_seq'::regclass)

...
Indexes:
"pk_id" PRIMARY KEY, btree (id)
...

# select * from pg_indexes where indexname='pk_id';
 schemaname | tablename | indexname | tablespace | 
 indexdef

+---+---++--
 public | player| pk_id || CREATE UNIQUE INDEX 
pk_id ON player USING btree (id)


# select * from pg_constraint where conname='pk_id';
-[ RECORD 1 ]-+--
conname   | pk_id
connamespace  | 2200
contype   | p
condeferrable | f
condeferred   | f
convalidated  | t
conrelid  | 18319
contypid  | 0
conindid  | 18640
confrelid | 0
confupdtype   |
confdeltype   |
confmatchtype |
conislocal| t
coninhcount   | 0
connoinherit  | t
conkey| {1}
confkey   |
conpfeqop |
conppeqop |
conffeqop |
conexclop |
conbin|
consrc|

# select count(*) from player where id=14875;
-[ RECORD 1 ]
count | 2

The records are identical, besides ctid,xmin,xmax

# select tableoid,ctid,id,xmin,xmax from player where id=14875;
 tableoid | ctid |id |xmin|xmax
--+--+---++
18319 | (9982129,2)  | 14875 | 3149449600 | 3152681810
18319 | (9976870,49) | 14875 | 3149448769 | 3152328995



I don't understand how this could have happened

--
Best regards,
Alexander Pyhalov, 3152328995
system administrator of Southern Federal University IT department

Re: [GENERAL] Reset Sequence number

[Wèi Cōngruì]

The 'SEQ' part can't be substituted by variable.
https://www.postgresql.org/docs/10/static/plpgsql-implementation.html

DO $$
 DECLARE
 SEQ BIGINT;
 BEGIN
  SEQ := (SELECT MAX(ID) FROM TABLE_1);
  EXECUTE format('ALTER SEQUENCE TABLE_1_SEQ RESTART WITH %s', SEQ);
 END
$$;

On Wed, Nov 22, 2017 at 6:26 PM, amul sul  wrote:

> Firstly, anonymous procedures are not supported in PostgreSQL, you need to
> embed this block in a plpgsql function[1] body & call that function if you
> want
> reset sequence value manually, or you could use CYCLE option[2] of a
> sequence to auto reset.
>
>
> 1] https://www.postgresql.org/docs/9.6/static/plpgsql-structure.html
> 2] https://www.postgresql.org/docs/devel/static/sql-createsequence.html
>
>
> Regards,
> Amul
>
> On Wed, Nov 22, 2017 at 3:06 PM, Brahmam Eswar 
> wrote:
> >
> > we are in process of migrating to postgres and need to reset the sequence
> > number with highest value of table key . I want to make it procedural to
> do
> > that as mentioned below,But it's throwing an error .
> >
> >
> >
> > DO $$
> >  DECLARE
> >  SEQ BIGINT;
> >  BEGIN
> >
> >   SEQ:=(SELECT MAX(ID) FROM TABLE_1);
> >   ALTER SEQUENCE TABLE_1_SEQ  RESTART WITH SEQ;
> >
> >  END$$;
> >
> > Error : syntax error at or near "SEQ"
> >
> > --
> > Thanks & Regards,
> > Brahmeswara Rao J.
>
>

Re: [GENERAL] Reset Sequence number

[Raymond O'Donnell]

On 22/11/17 09:36, Brahmam Eswar wrote:


we are in process of migrating to postgres and need to reset the 
sequence number with highest value of table key . I want to make it 
procedural to do that as mentioned below,But it's throwing an error .




DO $$
  DECLARE
  SEQ BIGINT;
  BEGIN
SEQ:=(SELECT MAX(ID) FROM TABLE_1);


select max(id) into seq from table_1;

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

Re: [GENERAL] Reset Sequence number

[amul sul]

Firstly, anonymous procedures are not supported in PostgreSQL, you need to
embed this block in a plpgsql function[1] body & call that function if you want
reset sequence value manually, or you could use CYCLE option[2] of a
sequence to auto reset.


1] https://www.postgresql.org/docs/9.6/static/plpgsql-structure.html
2] https://www.postgresql.org/docs/devel/static/sql-createsequence.html


Regards,
Amul

On Wed, Nov 22, 2017 at 3:06 PM, Brahmam Eswar  wrote:
>
> we are in process of migrating to postgres and need to reset the sequence
> number with highest value of table key . I want to make it procedural to do
> that as mentioned below,But it's throwing an error .
>
>
>
> DO $$
>  DECLARE
>  SEQ BIGINT;
>  BEGIN
>
>   SEQ:=(SELECT MAX(ID) FROM TABLE_1);
>   ALTER SEQUENCE TABLE_1_SEQ  RESTART WITH SEQ;
>
>  END$$;
>
> Error : syntax error at or near "SEQ"
>
> --
> Thanks & Regards,
> Brahmeswara Rao J.

[GENERAL] Reset Sequence number

[Brahmam Eswar]

we are in process of migrating to postgres and need to reset the sequence
number with highest value of table key . I want to make it procedural to do
that as mentioned below,But it's throwing an error .



DO $$
 DECLARE
 SEQ BIGINT;
 BEGIN

  SEQ:=(SELECT MAX(ID) FROM TABLE_1);
  ALTER SEQUENCE TABLE_1_SEQ  RESTART WITH SEQ;

 END$$;

Error : syntax error at or near "SEQ"

-- 
Thanks & Regards,
Brahmeswara Rao J.

Re: migrations (was Re: To all who wish to unsubscribe)

[Niels Kristian Schjødt]

The password reset form does not work. I do not get any reset emails when 
entering mine, so I can’t unsubscribe.

> Den 22. nov. 2017 kl. 04.24 skrev Stephen Frost :
> 
> Igor,
> 
> * Igor Korot (ikoro...@gmail.com) wrote:
>> Can anyone explain why?
>> Why not do what other ML do - provide the links in the footer?
> 
> This has been explained multiple times in multiple ways, including at
> https://wiki.postgresql.org/wiki/PGLister_Announce
> 
> Please drop it.  We won't be going back to having a footer and
> continuing to insist that the arguments outlined in the above post
> don't exist or aren't valid isn't going to change that.
> 
> If you'd really like to further discuss this, please take it off-list
> and contact me directly and we can chat about it, but this is not
> appropriate for this list any longer.
> 
> Thanks!
> 
> Stephen