Re: [GENERAL] pg_catalog tables don't have constraints?

[Tom Lane]

Neil Anderson  writes:
> I've been exploring the pg_catalog tables and pointed a couple of
> tools at it to extract an ER diagram for a blog post. At first I
> thought it was a bug in the drawing tool but it appears that the
> relationships between the pg_catalog tables are implicit rather than
> enforced by the database, is that correct?

Yep, there are no explicit FKs among the system catalogs.

> For example, pg_class has relnamespace which according to the
> documentation refers to pg_namespace.oid
> (https://www.postgresql.org/docs/current/static/catalog-pg-class.html),
> yet there is no FK there as far as I can see. Maybe I am missing
> something or there is an interesting story as to why?

It would be tough to do that without creating a lot of circularities.
>From the point of view of the low-level catalog manipulation code,
FKs are a high-level feature.

We don't have check constraints on system catalogs, either, for
largely similar reasons.  And while we do take the trouble to mark
some catalog columns NOT NULL, I'm pretty sure that's window
dressing: it's not actually checked on insertions driven from C code.

It'd be an interesting research project to see if such things could
be defined and enforced without getting into infinite recursions.
But even if it could be made to work, we'd probably only consider
enabling the constraints as a debug aid; in a production system,
testing them would just be overhead.

The bigger picture here is that catalog changes are supposed to be
executed by C code in response to DDL commands, and it's the C code
that is charged with maintaining catalog consistency.  Constraints
would be useful if we supported updating the catalogs with direct
SQL manipulations; but we don't really.  You can do that, if you're
a superuser who's willing to take risks, but our policy is that if
you break the catalogs that way you get to keep both pieces.

regards, tom lane


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

[GENERAL] pg_catalog tables don't have constraints?

[Neil Anderson]

Hi,

I've been exploring the pg_catalog tables and pointed a couple of
tools at it to extract an ER diagram for a blog post. At first I
thought it was a bug in the drawing tool but it appears that the
relationships between the pg_catalog tables are implicit rather than
enforced by the database, is that correct?

For example, pg_class has relnamespace which according to the
documentation refers to pg_namespace.oid
(https://www.postgresql.org/docs/current/static/catalog-pg-class.html),
yet there is no FK there as far as I can see. Maybe I am missing
something or there is an interesting story as to why?

Thanks,
Neil


-- 
Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.com



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

Re: [GENERAL] Redo the filenode link in tablespace

[tel medola]

Holy shit! (sorry)

Thanks, thanks!!!

It worked!

My goodness
After I point to the filnode, I did a reindex on the toast and some records
have already been located.



2017-06-07 17:58 GMT-03:00 Adrian Klaver :

> On 06/07/2017 07:53 AM, tel medola wrote:
>
>>
>> Change the relfilenode in above from 13741353 to 5214493
>> /I' no change yeat, but i will.../
>>
>>
>>
> What is not clear is what 5214495 is?
>> /Not to me either/
>>
>> select * from pg_class where relfilenode = 5214495;
>> /returns: none records/
>>
>> But I'm worried about the select error. You are returning the
>> table:pg_toast_9277966 not the pg_toast_5214489... bellow
>> ERROR:  missing chunk number 0 for toast value 10259186 in
>> pg_toast_9277966
>>
>> Could it be because of the filenode link that is still pointing to
>> another? -> 13741353
>>
>
>
> That is what I am betting.
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] How does BDR replicate changes among nodes in a BDR group

[Craig Ringer]

On 8 June 2017 at 04:50, Zhu, Joshua  wrote:

> How does BDR replicate a change delta on A to B, C, and D?

It's a mesh.

Once joined, it doesn't matter what the join node was, all nodes are equal.

> e.g., A
> replicates delta to B and D, and B to C, or some other way, or not
> statically determined?

Each node replicates to all other nodes in an undefined order
determined by network timing etc.


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


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

Re: [GENERAL] Redo the filenode link in tablespace

[Adrian Klaver]

On 06/07/2017 07:53 AM, tel medola wrote:


Change the relfilenode in above from 13741353 to 5214493
/I' no change yeat, but i will.../





What is not clear is what 5214495 is?
/Not to me either/

select * from pg_class where relfilenode = 5214495;
/returns: none records/

But I'm worried about the select error. You are returning the 
table:pg_toast_9277966 not the pg_toast_5214489... bellow

ERROR:  missing chunk number 0 for toast value 10259186 in pg_toast_9277966

Could it be because of the filenode link that is still pointing to 
another? -> 13741353



That is what I am betting.


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


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

[GENERAL] How does BDR replicate changes among nodes in a BDR group

[Zhu, Joshua]

New to this group, so if this is not the right place to ask this question or it 
has been asked before/documented, please kindly point me the right group or the 
right thread/documentation, thanks.

A BDR novice, I would like to know how BDR replicate changes among nodes in a 
BDR group, let's say I have a 4 node group consisting of A, B, C and D, 
established as follows

A is the initial node
B joins via A
C joins via B
D joins via A

How does BDR replicate a change delta on A to B, C, and D? e.g., A replicates 
delta to B and D, and B to C, or some other way, or not statically determined?
How about a change delta on B to A, C and D? e.g., B replicates delta to A and 
C, A to D, or?
How about a change delta on C to A, B and D? e.g., C replicates delta to B, B 
to A, A to D, or?
How about a change delta on D to A, B and C? e.g., D replicate delta to A, A to 
B, B to A, or?

Thanks

Re: [GENERAL] Advisory lock deadlock issue

[Adrian Klaver]

On 06/07/2017 08:11 AM, David Rosenstrauch wrote:



On 06/07/2017 10:32 AM, Merlin Moncure wrote:
On Wed, Jun 7, 2017 at 9:16 AM, David Rosenstrauch  
wrote:
* How could it be possible that there are 2 PG processes trying to 
acquire
the same lock?  Spark's partitioning should ensure that all updates 
to the
same user record get routed to the same process, so this situation 
shouldn't

even be possible.


That's really a question for the Spark team.  Obviously they are --
advisory locks lay on top of the basic locking mechanics and are very
well tested and proven.   What I can tell you is that in the core
functions provided by postgres there are no advisory locks thrown --
you own the locking space (that is, code under your control).


* How/why am I winding up acquiring advisory locks in the first 
place? I'm
never requesting them.  I looked at the PG JDBC driver code a bit, 
thinking

that it might automatically be creating them for some reason, but that
doesn't seem to be the case.  Maybe the PG database itself is? (E.g., 
Does

the PG automatically use advisory locks with UPSERTs?)


Some code under your control is. This could be an external module,
application code, or an sproc.



And, last but not least:

* How do I resolve this "waits for ExclusiveLock on advisory lock" 
issue?

There's precious little info available regarding exactly what that error
message is and how to solve.


Barring some reponse from Spark team, here is how I would narrow the
problem down:



merlin



Thanks much for the suggestions.  I'll look into them..

As far as the source of the advisory locks, I don't think they're coming 
from Spark as I'm not using any Spark code to access PG.  (Just straight 
JDBC.)


I'm actually using an offshoot of PG (CitusDB), so perhaps Citus is 
somehow initiating them.  I'll try to pin this down a bit further.


Aah that is an important piece of info. A quick search found:

https://www.citusdata.com/blog/2017/04/11/rebalancing-your-database-with-citus/

"
...

While this move is happening it takes a standard Postgres advisory locks 
..."






Thanks,

DR





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


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

Re: [GENERAL] Help with restoring a dump in Tar format? (dependencies/ordering)

[Tom Lane]

Ken Tanzer  writes:
>> FWIW, the business with making and editing a list file should work just
>> fine with a tar-format dump, not only with a custom-format dump.  The
>> metadata is all there in either case.

> The pg_dump doc page kinda suggests but doesn't quite say that you can't
> re-order tar files; between that and the error message I gave up on that
> possibility.  Are you suggesting it should work?

[ sorry for slow response ]

Ah, right: you can reorder simple object declarations, but you can't
change the relative order in which TABLE DATA objects are restored.
This is because the code doesn't support seeking in the tar file,
so it has to either read or skip each table-data subfile as it comes
to it.

It seems to me that that's just a small matter of programming to fix,
but few people use the tar format so nobody's bothered.

regards, tom lane


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

Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

[Tom Lane]

Harry Ambrose  writes:
> Tom - I can provide a jar that I have been using to replicate the issue. 
> Whats the best transport method to send it over?

If it's not enormous, just send it as an email attachment.

regards, tom lane


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

Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

[Harry Ambrose]

Hi,

Thanks for the responses.

> "One lesson I learned from the BSD camp when dealing with random freezes and 
> panics : when all else fails to give an answer it is time to start blaming my 
> hardware. Are those tablespaces on any cheap SSD's ?”

The tablespaces are not sat on SSD’s. Something I had also considered.

Tom - I can provide a jar that I have been using to replicate the issue. Whats 
the best transport method to send it over?

Best wishes,
Harry

> On 7 Jun 2017, at 16:27, Tom Lane  wrote:
> 
> Harry Ambrose  writes:
>> I have been following the updates to the 9.4 branch hoping a fix will 
>> appear, but sadly no luck yet. I have manually replicated the issue on 
>> 9.4.4, 9.4.10 and 9.4.12. My replication steps are:
> 
> This is a very interesting report, but you didn't actually provide a
> reproducer, just a handwavy outline.  If you submit a script that
> makes this happen, we will most definitely look into it.  But
> people aren't going to be excited about trying to reverse-engineer
> a test case out of a vague description.
> 
>> I also found the following has been reported:
>> https://www.postgresql.org/message-id/20161201165505.4360.28...@wrigleys.postgresql.org
> 
> That person never came back with a self-contained test case, either.
> 
> https://wiki.postgresql.org/wiki/Guide_to_reporting_problems
> 
>   regards, tom lane



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

Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

[Tom Lane]

Harry Ambrose  writes:
> I have been following the updates to the 9.4 branch hoping a fix will appear, 
> but sadly no luck yet. I have manually replicated the issue on 9.4.4, 9.4.10 
> and 9.4.12. My replication steps are:

This is a very interesting report, but you didn't actually provide a
reproducer, just a handwavy outline.  If you submit a script that
makes this happen, we will most definitely look into it.  But
people aren't going to be excited about trying to reverse-engineer
a test case out of a vague description.

> I also found the following has been reported:
> https://www.postgresql.org/message-id/20161201165505.4360.28...@wrigleys.postgresql.org

That person never came back with a self-contained test case, either.

https://wiki.postgresql.org/wiki/Guide_to_reporting_problems

regards, tom lane


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

Re: [GENERAL] Advisory lock deadlock issue

[David Rosenstrauch]

On 06/07/2017 10:32 AM, Merlin Moncure wrote:

On Wed, Jun 7, 2017 at 9:16 AM, David Rosenstrauch  wrote:

* How could it be possible that there are 2 PG processes trying to acquire
the same lock?  Spark's partitioning should ensure that all updates to the
same user record get routed to the same process, so this situation shouldn't
even be possible.


That's really a question for the Spark team.  Obviously they are --
advisory locks lay on top of the basic locking mechanics and are very
well tested and proven.   What I can tell you is that in the core
functions provided by postgres there are no advisory locks thrown --
you own the locking space (that is, code under your control).



* How/why am I winding up acquiring advisory locks in the first place? I'm
never requesting them.  I looked at the PG JDBC driver code a bit, thinking
that it might automatically be creating them for some reason, but that
doesn't seem to be the case.  Maybe the PG database itself is? (E.g., Does
the PG automatically use advisory locks with UPSERTs?)


Some code under your control is. This could be an external module,
application code, or an sproc.



And, last but not least:

* How do I resolve this "waits for ExclusiveLock on advisory lock" issue?
There's precious little info available regarding exactly what that error
message is and how to solve.


Barring some reponse from Spark team, here is how I would narrow the
problem down:



merlin



Thanks much for the suggestions.  I'll look into them..

As far as the source of the advisory locks, I don't think they're coming 
from Spark as I'm not using any Spark code to access PG.  (Just straight 
JDBC.)


I'm actually using an offshoot of PG (CitusDB), so perhaps Citus is 
somehow initiating them.  I'll try to pin this down a bit further.


Thanks,

DR


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

Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

[Achilleas Mantzios]

On 07/06/2017 17:49, Harry Ambrose wrote:

Hi,
Out of interest, are you using any tablespaces other than pg_default? I can 
only replicate the issue when using separately mounted tablespaces.
One lesson I learned from the BSD camp when dealing with random freezes and panics : when all else fails to give an answer it is time to start blaming my hardware. Are those tablespaces on any cheap 
SSD's ?


I have been investigating this quite extensively and everything I can find on the web suggests data corruption. However running the the following DO reports no errors and I can dump the database 
without issue.



You don't use index when pg_dump . If only the index is corrupted you can get 
away with dump/reload (but for big DBs this is unrealistic)

I also found the following has been reported: 
https://www.postgresql.org/message-id/20161201165505.4360.28...@wrigleys.postgresql.org

Best wishes,
Harry


On 7 Jun 2017, at 15:22, Achilleas Mantzios > wrote:

On 07/06/2017 16:33, ADSJ (Adam Sjøgren) wrote:

Our database has started reporting errors like this:

  2017-05-31 13:48:10 CEST ERROR:  unexpected chunk number 0 (expected 1) for 
toast value 14242189 in pg_toast_10919630
  ...
  2017-06-01 11:06:56 CEST ERROR:  unexpected chunk number 0 (expected 1) for 
toast value 19573520 in pg_toast_10919630

(157 times, for different toast values, same pg_toast_nnn). pg_toast_10919630
corresponds to a table with around 168 million rows.

These went away, but the next day we got similar errors from another
table:

  2017-06-02 05:59:50 CEST ERROR:  unexpected chunk number 0 (expected 1) for 
toast value 47060150 in pg_toast_10920100
  ...
  2017-06-02 06:14:54 CEST ERROR:  unexpected chunk number 0 (expected 1) for 
toast value 47226455 in pg_toast_10920100

(Only 4 this time) pg_toast_10920100 corresponds to a table with holds
around 320 million rows (these are our two large tables).

The next day we got 6 such errors and the day after 10 such errors. On
June 5th we got 94, yesterday we got 111, of which one looked a little
different:

  2017-06-06 17:32:21 CEST ERROR:  unexpected chunk size 1996 (expected 1585) 
in final chunk 0 for toast value 114925100 in pg_toast_10920100

and today the logs have 65 lines, ending with these:

  2017-06-07 14:49:53 CEST ERROR:  unexpected chunk number 0 (expected 1) for 
toast value 131114834 in pg_toast_10920100
  2017-06-07 14:53:41 CEST ERROR:  unexpected chunk number 0 (expected 1) for 
toast value 131149566 in pg_toast_10920100

First try to find which tables those toast relations refer to :
select 10919630::regclass , 10920100::regclass ;
Are those critical tables? Can you restore them somehow?

Also you may consider
REINDEX TABLE pg_toast.pg_toast_10920100;
REINDEX TABLE pg_toast.pg_toast_10919630;
REINDEX TABLE ;
REINDEX TABLE ;

also VACUUM the above tables.

You might want to write a function which iterates over the damaged table's rows 
in order to identify the damaged row(s). And then do some good update to create 
a new version.


The database is 10 TB on disk (SSDs) and runs on a 48 core server with 3
TB RAM on Ubuntu 14.04 (Linux 3.18.13).

We are updating rows in the database a lot/continuously.

There are no apparent indications of hardware errors (like ECC) in
dmesg, nor any error messages logged by the LSI MegaRAID controller, as
far as I can tell.

We are running PostgreSQL 9.3.14 currently.

The only thing I could see in the release notes since 9.3.14 that might
be related is this:

 "* Avoid very-low-probability data corruption due to testing tuple
visibility without holding buffer lock (Thomas Munro, Peter Geoghegan,
Tom Lane)"

Although reading more about it, it doesn't sound like it would exhibit
the symptoms we see?

We have recently increased the load (to around twice the number of
cores), though, which made me think we could be triggering corner cases
we haven't hit before.

We will be upgrading to PostgreSQL 9.3.17 during the weekend, but I'd like to 
hear
if anyone has seen something like this, or have some ideas of how to
investigate/what the cause might be.


  Best regards,

Adam



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



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




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

Re: [GENERAL] Redo the filenode link in tablespace

[tel medola]

Change the relfilenode in above from 13741353 to 5214493
*I' no change yeat, but i will...*


select * from pg_classs where reltoastrelid = 9277970
returns:

*   oid   |   relname   | relnamespace | reltype | reloftype | relowner |
relam | relfilenode | reltablespace | relpages |  reltuples   |
relallvisible | reltoastrelid | reltoastidxid | relhasindex | relisshared |
relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey |
relhasrules | relhastriggers | relhassubclass | relispopulated |
relfrozenxid | relminmxid | relacl | reloptions *
*-+-+--+-+---+--+---+-+---+--+--+---+---+---+-+-++-+--+---+++-++++--+++*
* 9277966 | repositorio |  9277964 | 9277968 | 0 |   10 |
  0 | 9277966 |   9277962 |43779 | 1.50905e+006 | 43779
|   9277970 | 0 | t   | f   | p
 | r   |6 | 0 | f  | t  | f   |
f  | f  | t  |  9360288 |
 1 || *
*(1 registro)*


What is not clear is what 5214495 is?
*Not to me either*

select * from pg_class where relfilenode = 5214495;
*returns: none records*

But I'm worried about the select error. You are returning the
table:pg_toast_9277966 not the pg_toast_5214489... bellow
ERROR:  missing chunk number 0 for toast value 10259186 in pg_toast_9277966

Could it be because of the filenode link that is still pointing to another?
-> 13741353


2017-06-06 21:08 GMT-03:00 Adrian Klaver :

> On 06/06/2017 02:07 PM, tel medola wrote:
>
>> rai=# select oid, * from pg_class where oid = 5214493;
>>
>> result:
>>
>>
>> oid   | relname  | relnamespace | reltype | reloftype |
>> relowner | relam | relfilenode | reltablespace | relpages | reltuples |
>> relallvisible | reltoastrelid | reltoastidxid | relhasindex | relisshared |
>> relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey |
>> relhasrules | relhastriggers | relhassubclass | relispopulated |
>> relfrozenxid | relminmxid | relacl | reloptions
>> -+--+--+-+--
>> -+--+---+-+---+-
>> -+---+---+---+--
>> -+-+-++-
>> +--+---+++--
>> ---++++-
>> -+++
>>   5214493 | pg_toast_5214489 |   99 | 5214494 | 0 |
>>   10 | 0 |13741353 |   5205910 |0 | 0 |
>>   0 | 0 |   5214495 | t   | f   | p
>>   | t   |3 | 0 | f  | t   | f
>>  | f  | f  | t  |   9360288
>> |  1 ||
>> (1 registro)
>>
>
> Change the relfilenode in above from 13741353 to 5214493
>
>
> If I do the select below (which is toast it returns me in error):
>> Select oid, * from pg_class where relname = 'pg_toast_9277966'
>> He will return me:
>>
>>
> Not sure here. I would try:
>
> select * from pg_classs where reltoastrelid = 9277970;
>
> to see what table is claiming it.
>
>
>
>> I do not know if it helps, but the folder structure looks like this:
>>
>
> The file names = the relfilenode + additional info
>
> 5214489 is your repositorio table
> 5214489_fsm is the Free Space Map for that table.
>
> 5214493 and 5214493.x is your TOAST table broken down into 1 GB chunks.
>
> What is not clear is what 5214495 is?
>
> select * from pg_class where relfilenode = 5214495;
>
> might help, unless it was also TRUNCATEd. In that case it depends on you
> knowing what other table then repositorio you had in the tablespace.
>
>
>>   Volume in drive G is Gravacoes-III
>>   Volume Serial Number is 8CF9-EDFA
>>
>>   Directory of g:\PG_9.3_201306121\32768
>>
>> 06/06/2017  13:18  .
>> 06/06/2017  13:18  ..
>> 23/05/2017  08:25 0 13741352
>> 23/05/2017  08:25 0 13741353
>> 06/06/2017  12:04 8.192 13789591
>> 20/05/2016  09:10   649.650.176 5214489
>> 20/05/2016  09:10   180.224 5214489_fsm
>> 19/05/2016  23:4716.384 5214489_vm
>> 25/04/2016  09:00 1.073.741.824 5214493
>> 25/04/2016  09:00 1.073.741.824 5214493.1
>> 25/04/2016  09:00 1.073.741.824 5214493.10
>> 25/04/2016  09:00 1.073.741.824 5214493.100
>> 25/04/2016  09:00 1.073.741.824 5214493.101
>> 25/04/2016  09:00 1.073.741.824 5214493.102
>> 25/04/2016  09:00 1.073.741.824 5214493.103

Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

[Harry Ambrose]

Hi,

I too have been experiencing this with a busy PostgreSQL instance.

I have been following the updates to the 9.4 branch hoping a fix will appear, 
but sadly no luck yet. I have manually replicated the issue on 9.4.4, 9.4.10 
and 9.4.12. My replication steps are:

BEGIN;
CREATE TABLE x (id BIGSERIAL PRIMARY KEY, payload1 VARCHAR, payload2 VARCHAR, 
payload3 VARCHAR, payload4 BIGINT, payload5 BIGINT);
/* Repeat until 2,000,000 rows are inserted */
INSERT INTO x (id, payload1, payload2, payload3, payload4, payload5) VALUES 
(random values of varying length/size to force random toast usage);
COMMIT;

VACUUM (ANALYZE, FULL);

BEGIN;
/* Repeat until all 2,000,000 rows are updated */
UPDATE x SET payload1 = , payload2 = , payload3 = , payload4 = , payload5 = ... 
again random values of varying length/size to force random toast usage
COMMIT;

VACCUM (ANALYZE, FULL);

The second vacuum causes an ERROR identical to that you are reporting below 
(unexpected chunk number n (expected n) for toast value...). However it may 
take up to ten attempts to replicate it.

Out of interest, are you using any tablespaces other than pg_default? I can 
only replicate the issue when using separately mounted tablespaces.

I have been investigating this quite extensively and everything I can find on 
the web suggests data corruption. However running the the following DO reports 
no errors and I can dump the database without issue.

DO $$
DECLARE

curid INT := 0;
vcontent RECORD;
badid BIGINT;

var1_sub VARCHAR;
var2_sub VARCHAR;
var3_sub VARCHAR;
var4_sub VARCHAR;
var5_sub VARCHAR;

BEGIN
FOR badid IN SELECT id FROM x 
LOOP
curid = curid + 1;

IF curid % 10 = 0 
THEN
RAISE NOTICE '% rows inspected', curid;
END IF;

BEGIN
SELECT *
INTO vcontent
FROM x
WHERE rowid = badid;

var1_sub := SUBSTR(vcontent.var1,2000,5000);
var2_sub := SUBSTR(vcontent.var2,2000,5000);   
var3_sub := SUBSTR(vcontent.var3,2000,5000);
var4_sub := SUBSTR(vcontent.var4::VARCHAR,2000,5000);
var5_sub := SUBSTR(vcontent.var5::VARCHAR,2000,5000);

EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 'Data for rowid % is corrupt', badid;
CONTINUE;
END;

END LOOP;
END;
$$;

I also found the following has been reported: 
https://www.postgresql.org/message-id/20161201165505.4360.28...@wrigleys.postgresql.org

Best wishes,
Harry

> On 7 Jun 2017, at 15:22, Achilleas Mantzios  
> wrote:
> 
> On 07/06/2017 16:33, ADSJ (Adam Sjøgren) wrote:
>> Our database has started reporting errors like this:
>> 
>>   2017-05-31 13:48:10 CEST ERROR:  unexpected chunk number 0 (expected 1) 
>> for toast value 14242189 in pg_toast_10919630
>>   ...
>>   2017-06-01 11:06:56 CEST ERROR:  unexpected chunk number 0 (expected 1) 
>> for toast value 19573520 in pg_toast_10919630
>> 
>> (157 times, for different toast values, same pg_toast_nnn). pg_toast_10919630
>> corresponds to a table with around 168 million rows.
>> 
>> These went away, but the next day we got similar errors from another
>> table:
>> 
>>   2017-06-02 05:59:50 CEST ERROR:  unexpected chunk number 0 (expected 1) 
>> for toast value 47060150 in pg_toast_10920100
>>   ...
>>   2017-06-02 06:14:54 CEST ERROR:  unexpected chunk number 0 (expected 1) 
>> for toast value 47226455 in pg_toast_10920100
>> 
>> (Only 4 this time) pg_toast_10920100 corresponds to a table with holds
>> around 320 million rows (these are our two large tables).
>> 
>> The next day we got 6 such errors and the day after 10 such errors. On
>> June 5th we got 94, yesterday we got 111, of which one looked a little
>> different:
>> 
>>   2017-06-06 17:32:21 CEST ERROR:  unexpected chunk size 1996 (expected 
>> 1585) in final chunk 0 for toast value 114925100 in pg_toast_10920100
>> 
>> and today the logs have 65 lines, ending with these:
>> 
>>   2017-06-07 14:49:53 CEST ERROR:  unexpected chunk number 0 (expected 1) 
>> for toast value 131114834 in pg_toast_10920100
>>   2017-06-07 14:53:41 CEST ERROR:  unexpected chunk number 0 (expected 1) 
>> for toast value 131149566 in pg_toast_10920100
> First try to find which tables those toast relations refer to :
> select 10919630::regclass , 10920100::regclass ;
> Are those critical tables? Can you restore them somehow?
> 
> Also you may consider
> REINDEX TABLE pg_toast.pg_toast_10920100;
> REINDEX TABLE pg_toast.pg_toast_10919630;
> REINDEX TABLE ;
> REINDEX TABLE ;
> 
> also VACUUM the above tables.
> 
> You might want to write a function which iterates over the damaged table's 
> rows in order to identify the damaged row(s). And then do some good 

Re: [GENERAL] Advisory lock deadlock issue

[Merlin Moncure]

On Wed, Jun 7, 2017 at 9:16 AM, David Rosenstrauch  wrote:
> I'm running a Spark job that is writing to a postgres db (v9.6), using the
> JDBC driver (v42.0.0), and running into a puzzling error:
>
> 2017-06-06 16:05:17.718 UTC [36661] dmx@dmx ERROR:  deadlock detected
> 2017-06-06 16:05:17.718 UTC [36661] dmx@dmx DETAIL:  Process 36661 waits for
> ExclusiveLock on advisory lock [16649,0,102440,5]; blocked by process 36662.
> Process 36662 waits for ExclusiveLock on advisory lock
> [16649,0,102439,5]; blocked by process 36661.
>
> However, I can't for the life of me figure out a) how the advisory locks are
> getting created (as I'm not calling for them myself), and b) how to fix this
> issue.
>
>
> A bit of background:  My Spark job runs as multiple processes on multiple
> machines.  Each process is performing the writes to pgsql using the jdbc
> driver.  The writes are performed a) as PG UPSERTS, b) as JDBC batches, and
> c) using JDBC prepared statements.  So each process, when it's time to write
> to the db, creates several prepared statements, adds a bunch of UPSERTs to
> each prepared statement (i.e., each prepared statement contains a batch of a
> few hundred UPSERTs), and then performs an executeBatch() on each statement
> to perform the write.  That executeBatch() call is where I'm running into
> the error.
>
> In theory, since there's multiple processes that are issuing these batched
> DB writes, there could be a record locking problem if, say, 2 processes
> tried to perform updates to the same user record.  But in reality this
> should be impossible.  Spark partitions everything based on a key - in my
> case userID - so all DB writes for the same user should be happening in the
> same process.  So at worst I could just have a batch that contains multiple
> UPSERTs to the same user record, but I should never be seeing updates to the
> same user from different processes.
>
>
> So, I'm very puzzled by that deadlock error.  Specifically:
>
> * How could it be possible that there are 2 PG processes trying to acquire
> the same lock?  Spark's partitioning should ensure that all updates to the
> same user record get routed to the same process, so this situation shouldn't
> even be possible.

That's really a question for the Spark team.  Obviously they are --
advisory locks lay on top of the basic locking mechanics and are very
well tested and proven.   What I can tell you is that in the core
functions provided by postgres there are no advisory locks thrown --
you own the locking space (that is, code under your control).

> * How/why am I winding up acquiring advisory locks in the first place? I'm
> never requesting them.  I looked at the PG JDBC driver code a bit, thinking
> that it might automatically be creating them for some reason, but that
> doesn't seem to be the case.  Maybe the PG database itself is? (E.g., Does
> the PG automatically use advisory locks with UPSERTs?)

Some code under your control is. This could be an external module,
application code, or an sproc.

> And, last but not least:
>
> * How do I resolve this "waits for ExclusiveLock on advisory lock" issue?
> There's precious little info available regarding exactly what that error
> message is and how to solve.

Barring some reponse from Spark team, here is how I would narrow the
problem down:

*) lets search the contents of pg_proc for functions calling advisory locks:
SELECT * FROM pg_proc where prosrc ~* 'advisory';

that might turn up some 3rd party code hits

*) turn on statement level logging and in bash:
tail -f postgres_xx.log | grep -i advisory

*) repeatedly query pg_locks for locktype = 'advisory'
SELECT * FROM pg_locks where locktype = 'advisory'

also,
SELECT * FROM pg_locks WHERE locktype = 'advisory' AND NOT granted;

Advisory locks are a very blunt instrument and it's a significant risk
that two different locking systems are stepping on each other's toes.
I do not recommend using them (especially non-xact variant) unless you
have total control over all the code potentially throwing locks and
have a good understanding of interactions with connection poolers
between locking code and the database.

merlin


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

Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

[Achilleas Mantzios]

On 07/06/2017 16:33, ADSJ (Adam Sjøgren) wrote:

Our database has started reporting errors like this:

   2017-05-31 13:48:10 CEST ERROR:  unexpected chunk number 0 (expected 1) for 
toast value 14242189 in pg_toast_10919630
   ...
   2017-06-01 11:06:56 CEST ERROR:  unexpected chunk number 0 (expected 1) for 
toast value 19573520 in pg_toast_10919630

(157 times, for different toast values, same pg_toast_nnn). pg_toast_10919630
corresponds to a table with around 168 million rows.

These went away, but the next day we got similar errors from another
table:

   2017-06-02 05:59:50 CEST ERROR:  unexpected chunk number 0 (expected 1) for 
toast value 47060150 in pg_toast_10920100
   ...
   2017-06-02 06:14:54 CEST ERROR:  unexpected chunk number 0 (expected 1) for 
toast value 47226455 in pg_toast_10920100

(Only 4 this time) pg_toast_10920100 corresponds to a table with holds
around 320 million rows (these are our two large tables).

The next day we got 6 such errors and the day after 10 such errors. On
June 5th we got 94, yesterday we got 111, of which one looked a little
different:

   2017-06-06 17:32:21 CEST ERROR:  unexpected chunk size 1996 (expected 1585) 
in final chunk 0 for toast value 114925100 in pg_toast_10920100

and today the logs have 65 lines, ending with these:

   2017-06-07 14:49:53 CEST ERROR:  unexpected chunk number 0 (expected 1) for 
toast value 131114834 in pg_toast_10920100
   2017-06-07 14:53:41 CEST ERROR:  unexpected chunk number 0 (expected 1) for 
toast value 131149566 in pg_toast_10920100

First try to find which tables those toast relations refer to :
select 10919630::regclass , 10920100::regclass ;
Are those critical tables? Can you restore them somehow?

Also you may consider
REINDEX TABLE pg_toast.pg_toast_10920100;
REINDEX TABLE pg_toast.pg_toast_10919630;
REINDEX TABLE ;
REINDEX TABLE ;

also VACUUM the above tables.

You might want to write a function which iterates over the damaged table's rows 
in order to identify the damaged row(s). And then do some good update to create 
a new version.


The database is 10 TB on disk (SSDs) and runs on a 48 core server with 3
TB RAM on Ubuntu 14.04 (Linux 3.18.13).

We are updating rows in the database a lot/continuously.

There are no apparent indications of hardware errors (like ECC) in
dmesg, nor any error messages logged by the LSI MegaRAID controller, as
far as I can tell.

We are running PostgreSQL 9.3.14 currently.

The only thing I could see in the release notes since 9.3.14 that might
be related is this:

  "* Avoid very-low-probability data corruption due to testing tuple
 visibility without holding buffer lock (Thomas Munro, Peter Geoghegan,
 Tom Lane)"

Although reading more about it, it doesn't sound like it would exhibit
the symptoms we see?

We have recently increased the load (to around twice the number of
cores), though, which made me think we could be triggering corner cases
we haven't hit before.

We will be upgrading to PostgreSQL 9.3.17 during the weekend, but I'd like to 
hear
if anyone has seen something like this, or have some ideas of how to
investigate/what the cause might be.


   Best regards,

 Adam



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



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

[GENERAL] Advisory lock deadlock issue

[David Rosenstrauch]

I'm running a Spark job that is writing to a postgres db (v9.6), using 
the JDBC driver (v42.0.0), and running into a puzzling error:


2017-06-06 16:05:17.718 UTC [36661] dmx@dmx ERROR:  deadlock detected
2017-06-06 16:05:17.718 UTC [36661] dmx@dmx DETAIL:  Process 36661 waits 
for ExclusiveLock on advisory lock [16649,0,102440,5]; blocked by 
process 36662.
Process 36662 waits for ExclusiveLock on advisory lock 
[16649,0,102439,5]; blocked by process 36661.


However, I can't for the life of me figure out a) how the advisory locks 
are getting created (as I'm not calling for them myself), and b) how to 
fix this issue.



A bit of background:  My Spark job runs as multiple processes on 
multiple machines.  Each process is performing the writes to pgsql using 
the jdbc driver.  The writes are performed a) as PG UPSERTS, b) as JDBC 
batches, and c) using JDBC prepared statements.  So each process, when 
it's time to write to the db, creates several prepared statements, adds 
a bunch of UPSERTs to each prepared statement (i.e., each prepared 
statement contains a batch of a few hundred UPSERTs), and then performs 
an executeBatch() on each statement to perform the write.  That 
executeBatch() call is where I'm running into the error.


In theory, since there's multiple processes that are issuing these 
batched DB writes, there could be a record locking problem if, say, 2 
processes tried to perform updates to the same user record.  But in 
reality this should be impossible.  Spark partitions everything based on 
a key - in my case userID - so all DB writes for the same user should be 
happening in the same process.  So at worst I could just have a batch 
that contains multiple UPSERTs to the same user record, but I should 
never be seeing updates to the same user from different processes.



So, I'm very puzzled by that deadlock error.  Specifically:

* How could it be possible that there are 2 PG processes trying to 
acquire the same lock?  Spark's partitioning should ensure that all 
updates to the same user record get routed to the same process, so this 
situation shouldn't even be possible.


* How/why am I winding up acquiring advisory locks in the first place? 
I'm never requesting them.  I looked at the PG JDBC driver code a bit, 
thinking that it might automatically be creating them for some reason, 
but that doesn't seem to be the case.  Maybe the PG database itself is? 
(E.g., Does the PG automatically use advisory locks with UPSERTs?)


And, last but not least:

* How do I resolve this "waits for ExclusiveLock on advisory lock" 
issue?  There's precious little info available regarding exactly what 
that error message is and how to solve.



Any help or pointers greatly appreciated!

Thanks,

DR


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

[GENERAL] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

[Adam Sjøgren]

Our database has started reporting errors like this:

  2017-05-31 13:48:10 CEST ERROR:  unexpected chunk number 0 (expected 1) for 
toast value 14242189 in pg_toast_10919630
  ...
  2017-06-01 11:06:56 CEST ERROR:  unexpected chunk number 0 (expected 1) for 
toast value 19573520 in pg_toast_10919630

(157 times, for different toast values, same pg_toast_nnn). pg_toast_10919630
corresponds to a table with around 168 million rows.

These went away, but the next day we got similar errors from another
table:

  2017-06-02 05:59:50 CEST ERROR:  unexpected chunk number 0 (expected 1) for 
toast value 47060150 in pg_toast_10920100
  ...
  2017-06-02 06:14:54 CEST ERROR:  unexpected chunk number 0 (expected 1) for 
toast value 47226455 in pg_toast_10920100

(Only 4 this time) pg_toast_10920100 corresponds to a table with holds
around 320 million rows (these are our two large tables).

The next day we got 6 such errors and the day after 10 such errors. On
June 5th we got 94, yesterday we got 111, of which one looked a little
different:

  2017-06-06 17:32:21 CEST ERROR:  unexpected chunk size 1996 (expected 1585) 
in final chunk 0 for toast value 114925100 in pg_toast_10920100

and today the logs have 65 lines, ending with these:

  2017-06-07 14:49:53 CEST ERROR:  unexpected chunk number 0 (expected 1) for 
toast value 131114834 in pg_toast_10920100
  2017-06-07 14:53:41 CEST ERROR:  unexpected chunk number 0 (expected 1) for 
toast value 131149566 in pg_toast_10920100

The database is 10 TB on disk (SSDs) and runs on a 48 core server with 3
TB RAM on Ubuntu 14.04 (Linux 3.18.13).

We are updating rows in the database a lot/continuously.

There are no apparent indications of hardware errors (like ECC) in
dmesg, nor any error messages logged by the LSI MegaRAID controller, as
far as I can tell.

We are running PostgreSQL 9.3.14 currently.

The only thing I could see in the release notes since 9.3.14 that might
be related is this:

 "* Avoid very-low-probability data corruption due to testing tuple
visibility without holding buffer lock (Thomas Munro, Peter Geoghegan,
Tom Lane)"

Although reading more about it, it doesn't sound like it would exhibit
the symptoms we see?

We have recently increased the load (to around twice the number of
cores), though, which made me think we could be triggering corner cases
we haven't hit before.

We will be upgrading to PostgreSQL 9.3.17 during the weekend, but I'd like to 
hear
if anyone has seen something like this, or have some ideas of how to
investigate/what the cause might be.


  Best regards,

Adam

-- 
 "Lägg ditt liv i min handAdam Sjøgren
  Sälj din själ till ett band"  a...@novozymes.com


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

Re: [GENERAL] Extract from text id other table - PG 9.1

[Jan de Visser]

On Tuesday, June 6, 2017 10:32:16 PM EDT Patrick B wrote:
> Hi guys,
> 
> I've got tableA with 3 columns.
> 
> id(seriaL) | type(character varying(256)) | string(character varying(256))
> 
> I have the type/string value stored in another table, and from that i would
> like to get the id.
> 
> Example:
> http://dbfiddle.uk/?rdbms=postgres_9.6=15c571caa36876f00a0a2eaace703a
> 2b
> 
> How can I extract, from that tablea.type_m column the tableb.id value?

SELECT b.id FROM tableb b, tablea a
  WHERE (b.type || '/' || b.string = a.type_m) AND (a.id = 2);



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

Re: [GENERAL] Writing a C function to return the log file name

[Kouber Saparev]

Thank you Laurenz,

I replaced "pg_time_t" with "Timestamp", yet the result looks the same -
each call returns random result.

2017-06-06 18:56 GMT+03:00 Albe Laurenz :

> Kouber Saparev wrote:
> > I am trying to write a function in C to return the log file name by
> given timestamp. I
> > will use that later to make dynamic creation of a foreign table
> (file_fdw) to read the csv
> > logs themselves. The thing is I do now want to hardcode neither the
> format, nor the
> > directory in my extension.
> >
> > I already looked into the adminpack extension, but the format is
> hardcoded to the default
> > one there, so it does not serve my needs.
> >
> > Here is what I currently have:
> > https://gist.github.com/kouber/89b6e5b647452a672a446b12413e20cf
> >
> >
> > The thing is the function is returning random results, obtained by
> pg_strftime().
> >
> > kouber=# select now()::timestamp, sqlog.log_path(now()::timestamp);
> > NOTICE:  Log directory = "pg_log"
> > NOTICE:  Log filename = "postgresql-%F.log"
> > NOTICE:  Length = "7"
> > NOTICE:  Filename = "pg_log/postgresql-17422165-04-30.log"
> >now |   log_path
> > +--
> > 2017-06-02 14:17:47.832446 | pg_log/postgresql-17422165-04-30.csv
> > (1 row)
> >
> > kouber=# select now()::timestamp, sqlog.log_path(now()::timestamp);
> > NOTICE:  Log directory = "pg_log"
> > NOTICE:  Log filename = "postgresql-%F.log"
> > NOTICE:  Length = "7"
> > NOTICE:  Filename = "pg_log/postgresql-17422166-02-08.log"
> >now |   log_path
> > +--
> > 2017-06-02 14:18:12.390558 | pg_log/postgresql-17422166-02-08.csv
> > (1 row)
> >
> >
> >
> >
> >
> > Any idea what am I doing wrong?
> >
> >
> > I copied logfile_getname() from syslogger.c, and simply added some debug
> messages in
> > there.
>
> You are mixing up "Timestamp" and "pg_time_t".
>
> Both are int64, but the former contains the number of microseconds since
> 2000-01-01 00:00:00, while the latter represents "the number of seconds
> elapsed since 00:00:00 on January 1, 1970, Coordinated Universal Time
> (UTC)"
> (quote from "man localtime").
>
> Yours,
> Laurenz Albe
>