Simple Query Doesn't Even with Data

[Scott Macri]

I have a simple database with table, "map_table", and coulmn,
"company_letters".  The database is populated and the first row
contains a value of 'abcdefg' in the company_letters column.

If I do a select * without a where clause I get the expected results. 
However, when I execute the following select statement I was astonished
to see no results come back.  

Any idea what could possibly be wrong here?  

I've verified the data a 100 times and am totally perplexed.  Thanks.

SELECT *
FROM public.map_table
WHERE company_letters = 'abcdefg';

-- 
Hacktorious

Re: Simple Query Doesn't Even with Data

[Julien Rouhaud]

Hi,

On Wed, Mar 09, 2022 at 08:40:45PM -0500, Scott Macri wrote:
>
> If I do a select * without a where clause I get the expected results.
> However, when I execute the following select statement I was astonished
> to see no results come back.
>
> SELECT *
> FROM public.map_table
> WHERE company_letters = 'abcdefg';

It looks like an index corruption, a REINDEX of that table should fix the
problem.

Did you update your system recently, which
may have updated your libc/libicu version, or replicated data over different OS
version (which could lead to the same problem)?  If yes, it's a known problem
and you have to reindex all indexes that uses collatable datatypes afterwards.

You can look at https://wiki.postgresql.org/wiki/Locale_data_changes for more
details about that problem.

Re: Simple Query Doesn't Even with Data

[Ken Tanzer]

On Thu, Mar 10, 2022 at 12:12 AM Scott Macri  wrote:

I've verified the data a 100 times and am totally perplexed.  Thanks.
>
> SELECT *
> FROM public.map_table
> WHERE company_letters = 'abcdefg';
>
>
Not sure what kinds of verification you did, but did you check to make sure
that there's no trailing whitespace?

What do you see for that row if you run this query?

SELECT
company_letters,length(company_letters),company_letters='abcdefg',trim(company_letters)='abcdefg'
FROM public.map_table;



-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

primary_conninfo and restore_command ?

[Luca Ferrari]

Hi all,
a friend of mine has shown to me a "strange" configuration of its
physical replication server (13): he has both primary_conninfo and
primary_slot_name, with replication slots active when queried on the
master. So far so good, but in the configuration he has also
restore_command to restore archived WALs from a centralized location.
Does this make sense?
Because if the replica cannot connect to the master, it will not start
at all (so I guess no restore_command will be executed). On the other
hand if the replica can connect to the primary the WALs will be
shipped by means of streaming.
Am I missing something?

Luca

Re: primary_conninfo and restore_command ?

[Julien Rouhaud]

Hi,

On Thu, Mar 10, 2022 at 02:51:16PM +0100, Luca Ferrari wrote:
> a friend of mine has shown to me a "strange" configuration of its
> physical replication server (13): he has both primary_conninfo and
> primary_slot_name, with replication slots active when queried on the
> master. So far so good, but in the configuration he has also
> restore_command to restore archived WALs from a centralized location.
> Does this make sense?
> Because if the replica cannot connect to the master, it will not start
> at all (so I guess no restore_command will be executed). On the other
> hand if the replica can connect to the primary the WALs will be
> shipped by means of streaming.
> Am I missing something?

We do have max_slot_wal_keep_size, and otherwise users could drop/create the
slot while the standby is shutdown if the situation is really bad, so it seems
sensible to me.

PQresultMemorySize of ResultSet larger than expected

[Dominique Devienne]

Hi,

I've instrumented by libpq code, to show the byte size of the result-set.
And I'm suprised that it's never smaller than 2,264 bytes, then the next
size I see is 3,288, etc... Even when receiving no rows at all, in fact.

I've trimmed-out the SQL, but these are all SELECT statements.
The output below is from Windows, if that matters.
The two larger ones at the bottom are fetching bytea blobs.

So, is this expected? I'm surprised 2KB+ is used to return no rows basically.
Where's the space going?

Is the reported space the same or similar to the actual bytes returned
on the wire?
Or they somehow add up local allocations client-side?
Any way to actually know how many bytes were returned on the wire?

I'm basically curious, and would appreciate pointers to better understand this.

Thanks, --DD

Running 1 test case...
...
`-> got 1 rows x 16 cols, 3,288 bytes (2.7 MB/s) in 1.159ms
...
`-> got 1 rows x 18 cols, 3,288 bytes (2.2 MB/s) in 1.417ms
...
`-> got 1 rows x 18 cols, 3,288 bytes (4.1 MB/s) in 0.771ms
...
`-> got 0 rows x 15 cols, 2,264 bytes (2.1 MB/s) in 1.041ms
...
`-> got 0 rows x 3 cols, 2,264 bytes (2.2 MB/s) in 0.991ms
...
`-> got 0 rows x 15 cols, 2,264 bytes (3.3 MB/s) in 0.662ms
...
`-> got 0 rows x 3 cols, 2,264 bytes (4.3 MB/s) in 0.503ms
...
`-> got 1 rows x 14 cols, 3,288 bytes (3.6 MB/s) in 0.863ms
...
`-> got 1 rows x 16 cols, 3,288 bytes (5.0 MB/s) in 0.623ms
...
`-> got 0 rows x 15 cols, 2,264 bytes (3.6 MB/s) in 0.602ms
...
`-> got 0 rows x 3 cols, 2,264 bytes (4.4 MB/s) in 0.494ms
...
`-> got 0 rows x 15 cols, 2,264 bytes (3.7 MB/s) in 0.591ms
...
`-> got 0 rows x 3 cols, 2,264 bytes (3.9 MB/s) in 0.548ms
...
`-> got 1 rows x 1 cols, 818,441 bytes (78.1 MB/s) in 9.996ms
...
`-> got 1 rows x 1 cols, 3,288 bytes (7.9 MB/s) in 0.396ms
...
`-> got 1 rows x 1 cols, 36,345 bytes (33.4 MB/s) in 1.038ms

*** No errors detected
*** tests_acme_foo in 2.926s (user: 0.219s) 38 MB

Press any key to continue . . .

Re: primary_conninfo and restore_command ?

[Bharath Rupireddy]

On Thu, Mar 10, 2022 at 7:22 PM Luca Ferrari  wrote:
>
> Hi all,
> a friend of mine has shown to me a "strange" configuration of its
> physical replication server (13): he has both primary_conninfo and
> primary_slot_name, with replication slots active when queried on the
> master. So far so good, but in the configuration he has also
> restore_command to restore archived WALs from a centralized location.
> Does this make sense?
> Because if the replica cannot connect to the master, it will not start
> at all (so I guess no restore_command will be executed). On the other
> hand if the replica can connect to the primary the WALs will be
> shipped by means of streaming.
> Am I missing something?

Yes, restore_command gets executed even in standby mode when
walreceiver is unable to receive wal from primary i.e. primary stopped
sending WAL, see some comments on it [1]. It looks like
restore_command in standby mode isn't mandatory, but is advisable I
guess. I hope that clarifies your question.

[1] WaitForWALToBecomeAvailable:

 *
 * If standby mode is turned off while reading WAL from stream, we move
 * to XLOG_FROM_ARCHIVE and reset lastSourceFailed, to force fetching
 * the files (which would be required at end of recovery, e.g., timeline
 * history file) from archive or pg_wal. We don't need to kill WAL receiver
 * here because it's already stopped when standby mode is turned off at
 * the end of recovery.
 *---
 */

   case XLOG_FROM_STREAM:

/*
 * Failure while streaming. Most likely, we got here
 * because streaming replication was terminated, or
 * promotion was triggered. But we also get here if we
 * find an invalid record in the WAL streamed from master,
 * in which case something is seriously wrong. There's
 * little chance that the problem will just go away, but
 * PANIC is not good for availability either, especially
 * in hot standby mode. So, we treat that the same as
 * disconnection, and retry from archive/pg_wal again. The
 * WAL in the archive should be identical to what was
 * streamed, so it's unlikely that it helps, but one can
 * hope...
 */

Regards,
Bharath Rupireddy.

Re: PQresultMemorySize of ResultSet larger than expected

[Tom Lane]

Dominique Devienne  writes:
> I've instrumented by libpq code, to show the byte size of the result-set.
> And I'm suprised that it's never smaller than 2,264 bytes, then the next
> size I see is 3,288, etc... Even when receiving no rows at all, in fact.

See PGRESULT_DATA_BLOCKSIZE and associated comments, starting about
line 90 in fe-exec.c.  I don't recall whether there was any specific
motivation for PGRESULT_DATA_BLOCKSIZE to be 2K rather than some
other value; but it wouldn't do for it to be very small.

regards, tom lane

foreign key on delete cascade order?

[George Woodring]

When using FOREIGN KEY ON DELETE CASCADE, is there an order the entries are
being deleted?

We are seeing intermittent deadlocks with trying to update a table with the
foreign key entry being deleted.

We have 4 levels of tables chained by foreign keys.

machine -> point -> poll -> status

The status is the only one updated constantly and we are trying to figure
out how to order the update to avoid the deadlock when the machine is
deleted.

Thanks for your help
George
iGLASS Networks
www.iglass.net

Re: foreign key on delete cascade order?

[Tom Lane]

George Woodring  writes:
> When using FOREIGN KEY ON DELETE CASCADE, is there an order the entries are
> being deleted?

No, there's no particular attempt to order the deletions.  Each cascaded
delete ought to be removing a disjoint set of rows in the referencing
table, so I'm not quite sure why order should matter.

regards, tom lane

[pgpool] invalid read kind

[Peter Juhasz]

Hi all,

we have a setup where we use PGPOOL instances between our application
and the main DB server. Lately there is an intermittent but annoying
error that sometimes pops up:


ERROR:  unable to parse the query
DETAIL:  invalid read kind
SSL SYSCALL error: EOF detected

Version numbers:
pgpool 3.7.20
postgresql 9.5.25


Relevant part of pgpool's log:

2022-03-10T09:44:50.00+00:00 sanitized_hostname pgpool[8731]:
[2992409-1] 2022-03-10 09:44:50: pid 8731: LOG:  statement: SELECT 'OK'
2022-03-10T09:44:50.00+00:00 sanitized_hostname pgpool[8731]:
[2992410-1] 2022-03-10 09:44:50: pid 8731: LOG:  DB node id: 0 backend
pid: 29433 statement: SELECT 'OK'
2022-03-10T09:44:50.00+00:00 sanitized_hostname pgpool[8731]:
[2992411-1] 2022-03-10 09:44:50: pid 8731: ERROR:  unable to parse the
query
2022-03-10T09:44:50.00+00:00 sanitized_hostname pgpool[8731]:
[2992411-2] 2022-03-10 09:44:50: pid 8731: DETAIL:  invalid read kind
2022-03-10T09:44:50.00+00:00 sanitized_hostname pgpool[8731]:
[2992412-1] 2022-03-10 09:44:50: pid 8731: LOG:  statement:  DISCARD
ALL
2022-03-10T09:44:50.00+00:00 sanitized_hostname pgpool[8731]:
[2992413-1] 2022-03-10 09:44:50: pid 8731: LOG:  DB node id: 0 backend
pid: 29433 statement:  DISCARD ALL
2022-03-10T09:44:50.00+00:00 sanitized_hostname pgpool[8731]:
[2992414-1] 2022-03-10 09:44:50: pid 8731: FATAL:  failed to read kind
from backend 0
2022-03-10T09:44:50.00+00:00 sanitized_hostname pgpool[8731]:
[2992414-2] 2022-03-10 09:44:50: pid 8731: DETAIL:  kind == 0

The query does not appear to be relevant, it's well-formed and not the
same every time.

Now, what does this mean, and how to deal with it?

Best regards,
Peter Juhasz

Comnica Kft.

Re: foreign key on delete cascade order?

[George Woodring]

On Thu, Mar 10, 2022 at 10:56 AM Tom Lane  wrote:

>  Each cascaded delete ought to be removing a disjoint set of rows in the
> referencing

table, so I'm not quite sure why order should matter.
>
> regards, tom lane
>

I have always thought the way to avoid deadlocks was to update rows in the
same order by the different updaters.  Is there a better chain of thought
for updating and deleting rows at the same time?  Do we need to put a lock
on the table to update, then have the delete queue up waiting for the lock
to be removed?

Thanks,
George

Re: foreign key on delete cascade order?

[Tom Lane]

George Woodring  writes:
> I have always thought the way to avoid deadlocks was to update rows in the
> same order by the different updaters.  Is there a better chain of thought
> for updating and deleting rows at the same time?  Do we need to put a lock
> on the table to update, then have the delete queue up waiting for the lock
> to be removed?

With the amount of detail you've provided (viz: none), it's impossible
for anyone to guess what your problem actually is, let alone speculate
on suitable solutions.

regards, tom lane