Re: Subscription statistics are not dropped at DROP SUBSCRIPTION in some cases

[Amit Kapila]

On Tue, May 16, 2023 at 8:00 PM Masahiko Sawada  wrote:
>
> On Thu, May 11, 2023 at 5:12 PM Masahiko Sawada  wrote:
> >
>
> After thinking more about it, I realized that this is not a problem
> specific to HEAD. ISTM the problem is that by commit 7b64e4b3, we drop
> the stats entry of subscription that is not associated with a
> replication slot for apply worker, but we missed the case where the
> subscription is not associated with both replication slots for apply
> and tablesync. So IIUC we should backpatch it down to 15.
>

I agree that it should be backpatched to 15.

> Since in pg15, since we don't create the subscription stats at CREATE
> SUBSCRIPTION time but do when the first error is reported,
>

AFAICS, the call to pgstat_create_subscription() is present in
CreateSubscription() in 15 as well, so, I don't get your point.

-- 
With Regards,
Amit Kapila.

Re: Incorrect estimation of HashJoin rows resulted from inaccurate small table statistics

[Tomas Vondra]

On 6/17/23 00:32, Quan Zongliang wrote:
> ...
>
> It's not just a small table. If a column's value is nearly unique. It
> also causes the same problem because we exclude values that occur only
> once. samplerows <= num_mcv just solves one scenario.
> Perhaps we should discard this (dups cnt > 1) restriction?
> 

But for larger tables we'll be unable to keep all the values in the MCV.
So I think this only can change things for tiny tables.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Deleting prepared statements from libpq.

[jian he]

On Fri, Jun 16, 2023 at 11:28 PM Jelte Fennema  wrote:
>
> On Fri, 16 Jun 2023 at 16:26, Craig Ringer  wrote:
> > Nobody's implemented it.
> >
> > A patch to add PQclosePrepared and PQsendClosePrepared would be welcome. At 
> > least, I think so...
>
> This might have been a pretty old thread. But I just took it upon me
> to implement these functions (or well I mostly copied the
> PQsendDescribe related code and did s/describe/close). I haven't
> tested this code yet but I'm pretty sure it should just work (it
> compiles at least).
>
> The main reason I'm interested in this is because we're actively
> working on implementing named prepared statement support for PgBouncer
> in transaction pooling mode. It works with lots of client libraries
> already. But sadly it doesn't work with psycopg at the moment, or at
> least the closing part does not. And the reason is that psycopg closes
> statements using a DEALLOCATE query instead of the Close protocol
> message, because libpq does not support sending the Close protocol
> message.
>
> Afaict this is not just a problem for PgBouncer its implementation. As
> far as I can tell the same is true for the Odyssey connection pooler
> (which implemented named prepared statement support first).


I failed to link it. I don't know why.
if I comment out {assert(PQsendClosePrepared(conn,stmtName) == 1);}
then it works.
I use the following 4 commands, they all yield results, so it seems
PQsendClosePrepare is there.

nm /home/jian/postgres/pg16_test/lib/libpq.so.5.16 | grep PQsendClosePrepare
nm /home/jian/postgres/pg16_test/lib/libpq.so.5 | grep PQsendClosePrepare
 nm /home/jian/postgres/pg16_test/lib/libpq.so | grep PQsendClosePrepare
 nm /home/jian/postgres/pg16_test/lib/libpq.a | grep PQsendClosePrepare

--
/*
gcc -I/home/jian/postgres/pg16_test/include
/home/jian/misc/testlibpq.c -L/home/jian/postgres/pg16_test/lib  -lpq

// nm /home/jian/postgres/pg16_test/lib/libpq.so | grep PQsendClosePrepared
0008df90 b __gcov0.PQsendClosePrepared
0007fda0 d __gcov_.PQsendClosePrepared
0002d060 t PQsendClosePrepared
*/

#include 
#include 
#include "libpq-fe.h"
#include 

void exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
};

int main()
{
char*conninfo = "dbname=test16beta port=5455 host=/tmp user=jian";
PGconn*conn;
PGresult   *res;

 /* Make a connection to the database */
conn = PQconnectdb(conninfo);

/* Check to see that the backend connection was successfully made */
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn));
exit_nicely(conn);
}

// PREPARE q3(text, int, float, boolean, smallint) AS
// SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
// ten = $3::bigint OR true = $4 OR odd = $5::int)
// ORDER BY unique1;
// EXECUTE q3('xx', 5::smallint, 10.5::float, false, 4::bigint);
// select 
'text'::regtype::oid,'int'::regtype::oid,'float'::regtype::oid,'bool'::regtype::oid,'smallint'::regtype::oid;

const char *stmtName = "q3";
const char *query  =
"SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR "
"ten = $3::bigint OR true = $4 OR odd = $5::int) "
"ORDER BY unique1 ";
int nParams = 5;
const Oid oidTypes[5] = {25, 23, 701,16, 21};
const char *const paramValues[] = {"xx","5","10.5","false","4"};


res = PQprepare(conn, stmtName, query,nParams, oidTypes);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
printf("here %d\n",__LINE__);
fprintf(stderr, "PQprepare failed: %s\n",PQresultErrorMessage(res));
PQclear(res);
}
else
{
PQclear(res);

res = PQexecPrepared(conn, stmtName, 5, paramValues,NULL, NULL,0);

if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
PQclear(res);
printf("PQexecPrepared failed\n");
exit_nicely(conn);
}
assert(PQsendClosePrepared(conn,stmtName) == 1);

res = PQexec(conn,"select from pg_prepared_statements where
name = 'q3'");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
PQclear(res);
printf("this command should return zero rows now it's not\n");
exit_nicely(conn);
}
}

PQclear(res);
/* close the connection to the database and cleanup */
PQfinish(conn);

return 0;
}

Re: Incorrect estimation of HashJoin rows resulted from inaccurate small table statistics

[Tomas Vondra]

On 6/17/23 02:02, Quan Zongliang wrote:
> 
> 
> On 2023/6/17 06:46, Tom Lane wrote:
>> Quan Zongliang  writes:
>>> Perhaps we should discard this (dups cnt > 1) restriction?
>>
>> That's not going to happen on the basis of one test case that you
>> haven't even shown us.  The implications of doing it are very unclear.
>> In particular, I seem to recall that there are bits of logic that
>> depend on the assumption that MCV entries always represent more than
>> one row.  The nmultiple calculation Tomas referred to may be failing
>> because of that, but I'm worried about there being other places.
>>

I don't recall any logic that'd outright fail with MCVs containing
single-row groups, and I haven't noticed anything obvious in analyze.c
during a cursory search. Maybe the paper analyze_mcv_list builds on
makes some assumptions? Not sure.

However, compute_distinct_stats() doesn't seem to have such protection
against single-row MCV groups, so if that's wrong we kinda already have
the issue I think (admittedly, compute_distinct_stats is much less used
than compute_scalar_stats).

> 
> The statistics for the other table look like this:
> stadistinct | 6
> stanumbers1 | {0.50096667,0.49736667,0.0012}
> stavalues1  | {v22,v23,v5}
> 
> The value that appears twice in the small table (v1 and v2) does not
> appear here. The stadistinct's true value is 18 instead of 6 (three
> values in the small table do not appear here).
> 
> When calculating the selectivity:
> if (nd2 > sslot2->nvalues)
>   totalsel1 += unmatchfreq1 * otherfreq2 / (nd2 - sslot2->nvalues);
> 
> totalsel1 = 0
> nd2 = 21
> sslot2->nvalues = 2
> unmatchfreq1 = 0.0002016420476
> otherfreq2 = 0.82608695328235626
> 
> result: totalsel1 = 0.043473913749706022
> rows = 0.043473913749706022 * 23 * 2,000,000 = 1999800
> 

Attached is a script reproducing this.

I think the fundamental issue here is that the most common element of
the large table - v22 (~50%) is not in the tiny one at all. IIRC the
join estimation assumes the domain of one table is a subset of the
other. The values 22 / 23 violate that assumption, unfortunately.

Including all values into the small MCV fix this because then

  otherfreq1 = 0.0

and that simply eliminates the impact of stuff that didn't have a match
between the two MCV lists. Which mitigates the violated assumption.

But once the small table gets too large for the MCV, this won't work
that well - it probably helps a bit, as it makes otherfreq1 smaller.

Which doesn't mean it's useless, but it's likely a rare combination that
a table is (and remains) smaller than MCV, and the large table contains
values without a match in the smaller one (think foreign keys).

> 
>> Basically, you're proposing a rather fundamental change in the rules
>> by which Postgres has gathered statistics for decades.  You need to
>> bring some pretty substantial evidence to support that.  The burden
>> of proof is on you, not on the status quo.
>>

Right. It's a good example of a "quick hack" fixing one particular case,
without considering the consequences on other cases too much. Good as a
starting point, but plenty of legwork to do.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

script.sql
Description: application/sql

Re: run pgindent on a regular basis / scripted manner

[Andrew Dunstan]

On 2023-06-15 Th 12:12, Andrew Dunstan wrote:



On 2023-06-15 Th 11:26, Jelte Fennema wrote:

On Sat, 22 Apr 2023 at 13:42, Andrew Dunstan  wrote:

Perhaps we should start with a buildfarm module, which would run pg_indent 
--show-diff. That would only need to run on one animal, so a failure wouldn't 
send the whole buildfarm red. This would be pretty easy to do.

Just to be clear on where we are. Is there anything blocking us from
doing this, except for the PG16 branch cut? (that I guess is planned
somewhere in July?)

Just doing this for pgindent and not for perltidy would already be a
huge improvement over the current situation IMHO.



The short answer is that some high priority demands from $dayjob got 
in the way. However, I hope to have it done soon.





See 




I have set up a new buildfarm animal called koel which will run the module.


cheers


andrew


--
Andrew Dunstan
EDB:https://www.enterprisedb.com

Re: test_extensions: fix inconsistency between meson.build and Makefile

[Gurjeet Singh]

On Fri, Jun 16, 2023 at 1:56 PM Tristan Partin  wrote:
>
> On Fri Jun 16, 2023 at 3:29 PM CDT, Jeff Davis wrote:
> > Patch attached. Currently, the Makefile specifies NO_LOCALE=1, and the
> > meson.build does not.
>
> Looks alright to me, but it might be nicer to change the order of
> arguments to match contrib/unaccent/meson.build:40. Might help with
> grepping in the future.

It seems that Jeff's patch tried to match the precedent set in
src/test/modules/test_oat_hooks/meson.build.

No matter which ordering Jeff's patch uses, it will be inconsistent
with one of the existing order of the options.

So attached is updated patch that makes the order consistent across
all 3 occurrences.

Best regards,
Gurjeet
http://Gurje.et


v2-0001-test_extensions-make-meson.build-consistent-with-.patch
Description: Binary data

Re: [PATCH] hstore: Fix parsing on Mac OS X: isspace() is locale specific

[Evan Jones]

Unfortunately I just noticed a possible "bug" with this change. The
scanner_isspace() function only recognizes *five* ASCII space characters: '
' \t \n \r \f. It *excludes* VTAB \v, which the C standard function
isspace() includes. This means this patch changed the behavior of hstore
parsing for some "unusual" cases where the \v character was previously
ignored, and now is not, such as: "select 'k=>\vv'::hstore" . It seems
unlikely to me that anyone would be depending on this. The
application/programming language library would need to be explicitly
depending on VTAB being ignored as leading/trailing characters for hstore
key/values. I am hopeful that most implementations encode hstore values the
same way Postgres does: always using quoted strings, which avoids this
problem.

However, if we think this change could be a problem, one fix would be to
switch scanner_isspace() to array_isspace(), which returns true for these
*six* ASCII characters. I am happy to submit a patch to do this.

However, I am now wondering if the fact that scanner_isspace() and
array_isspace() disagree with each other could be problematic somewhere,
but so far I haven found anything.


Problematic example before my hstore change:

$ printf "select 'k=>\vv'::hstore" | psql
  hstore
--
 "k"=>"v"
(1 row)

Same example after my hstore change on postgres master commit a14e75eb0b
from 2023-06-16:

$ printf "select 'k=>\vv'::hstore" | psql
hstore
--
 "k"=>"\x0Bv"
(1 row)




On Sun, Jun 11, 2023 at 8:18 PM Michael Paquier  wrote:

> On Tue, Jun 06, 2023 at 10:16:09AM -0400, Evan Jones wrote:
> > I did a quick look at the places found with "git grep isspace"
> yesterday. I
> > agree with the comment from commit 9ae2661: "I've left alone isspace()
> > calls in places that aren't really expecting any non-ASCII input
> > characters, such as float8in()." There are a number of other calls where
> I
> > think it would likely be safe, and possibly even a good idea, to replace
> > isspace() with scanner_isspace(). However, I couldn't find any where I
> > could cause a bug like the one I hit in hstore parsing.
>
> Yes, I agree with this feeling.  Like 9ae2661, I can't get really
> excited about plastering more of that, especially if it were for
> timezone value input or dictionary options.  One area with a new
> isspace() since 2017 is multirangetypes.c, but it is just a copy of
> rangetypes.c.
>
> > Original mailing list post for commit 9ae2661 in case it is helpful for
> > others:
> https://www.postgresql.org/message-id/10129.1495302...@sss.pgh.pa.us
>
> I have reproduced the original problem reported on macOS 13.4, which
> is close to the top of what's available.
>
> Passing to pg_regress some options to use something else than UTF-8
> leads to a failure in the tests, so we need a split like
> fussyztrmatch to test that:
> REGRESS_OPTS='--encoding=SQL_ASCII --no-locale' make check
>
> An other error pattern without a split could be found on Windows, as
> of:
>  select E'key\u0105=>value'::hstore;
> - hstore
> --
> - "keyÄ…"=>"value"
> -(1 row)
> -
> +ERROR:  character with byte sequence 0xc4 0x85 in encoding "UTF8" has
> no equivalent in encoding "WIN1252"
> +LINE 1: select E'key\u0105=>value'::hstore;
>
> We don't do that for unaccent, actually, leading to similar failures..
> I'll launch a separate thread about that shortly.
>
> With that fixed, the fix has been applied and backpatched.  Thanks for
> the report, Evan!
> --
> Michael
>

[PATCH] ltree hash functions

[Tommy Pavlicek]

Hi All,

I've written a patch to add hash functions for the ltree extension. It adds
support for hash indexes and hash aggregation. I've reused the existing
logic that's used to hash arrays and added tests that mirror elsewhere
(i.e. hstore and hash_func regression tests).

The patch doesn't currently support hash joins as the ltree = operator was
created without support for it. The ALTER OPERATOR command doesn't support
changing the hash join support, so I'm not sure what the best strategy to
change it is. Is it ok to update the operator's row in the pg_operator
system catalog or is there a better way to change this that someone could
recommend?

Any comments on the overall approach or other feedback would be appreciated.

Thanks,
Tommy


ltree-hash-v1.patch
Description: Binary data

Re: [17] CREATE COLLATION default provider

[Gurjeet Singh]

On Wed, Jun 14, 2023 at 9:48 PM Jeff Davis  wrote:
>
> Currently, CREATE COLLATION always defaults the provider to libc.
>
> The attached patch causes it to default to libc if LC_COLLATE/LC_CTYPE
> are specified, otherwise default to the current database default
> collation's provider.

+if (lccollateEl || lcctypeEl)
+collprovider = COLLPROVIDER_LIBC;
+else
+collprovider = default_locale.provider;

The docs for the CREATE COLLATION option 'locale' say: "This is a
shortcut for setting LC_COLLATE and LC_CTYPE at once."

So it's not intuitive why the check does not include a test for the
presence of 'localeEl', as well? If we consider the presence of
LC_COLLATE _or_ LC_CTYPE options to be a determining factor for some
decision, then the presence of LOCALE option should also lead to the
same outcome.

Otherwise the patch looks good.

> v11-0001-CREATE-COLLATION-default-provider.patch

I believe v11 is a typo, and you really meant v1.

Best regards,
Gurjeet
http://Gurje.et

PostgreSQL 16 Beta 2 Release Date

[Jonathan S. Katz]

Hi,

The release date for PostgreSQL 16 Beta 2 is June 29, 2023. Please be 
sure to commit any open items[1] for the Beta 2 release before June 25, 
2023 0:00 AoE[2] to give them enough time to work through the buildfarm.


Thanks,

Jonathan

[1] 
https://wiki.postgresql.org/wiki/PostgreSQL_16_Open_Items#Important_Dates

[2] https://en.wikipedia.org/wiki/Anywhere_on_Earth


OpenPGP_signature
Description: OpenPGP digital signature

Re: [PATCH] ltree hash functions

[Tomas Vondra]

Hi,

I've created a CF entry for the patch:

  https://commitfest.postgresql.org/43/4375/

I only briefly skimmed the code, so a couple comments.

On 6/17/23 17:45, Tommy Pavlicek wrote:
> Hi All,
> 
> I've written a patch to add hash functions for the ltree extension. It
> adds support for hash indexes and hash aggregation. I've reused the
> existing logic that's used to hash arrays and added tests that mirror
> elsewhere (i.e. hstore and hash_func regression tests).
> 

Reusing code/logic is the right approach, IMHO.

> The patch doesn't currently support hash joins as the ltree = operator
> was created without support for it. The ALTER OPERATOR command doesn't
> support changing the hash join support, so I'm not sure what the best
> strategy to change it is. Is it ok to update the operator's row in the
> pg_operator system catalog or is there a better way to change this that
> someone could recommend?
> 

I guess the "correct" solution would be to extend ALTER OPERATOR. I
wonder why it's not supported - it's clearly an intentional decision
(per comment in AlterOperator). So what might break if this changes for
an existing operator?

FWIW the CREATE OPERATOR documentation only talks about hash joins for
HASHES, maybe it should be updated to also mention hash aggregates?

> Any comments on the overall approach or other feedback would be appreciated.
> 

I wonder what's the use case for this. I wonder how often people join on
ltree, for example. Did you just notice ltree can't hash and decided to
fix that, or do you have a practical use case / need for this feature?


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [PATCH] ltree hash functions

[Tom Lane]

Tomas Vondra  writes:
> I guess the "correct" solution would be to extend ALTER OPERATOR. I
> wonder why it's not supported - it's clearly an intentional decision
> (per comment in AlterOperator). So what might break if this changes for
> an existing operator?

This code was added by commit 321eed5f0.  The thread leading up to
that commit is here:

https://www.postgresql.org/message-id/flat/3348985.V7xMLFDaJO%40dinodell

There are some nontrivial concerns in there about breaking the
semantics of existing exclusion constraints, for instance.  I think
we mostly rejected the concern about invalidation of cached plans
as already-covered, but that wasn't the only problem.

However, I think we could largely ignore the issues if we restricted
ALTER OPERATOR to only add commutator, negator, hashes, or merges
properties to operators that lacked them before --- which'd be the
primary if not only use-case anyway.  That direction can't break
anything.

regards, tom lane

Assert while autovacuum was executing

[Jaime Casanova]

Hi,

I have been testing 16beta1, last commit
a14e75eb0b6a73821e0d66c0d407372ec8376105
I just let sqlsmith do its magic before trying something else, and
today I found a core with the attached backtrace.

Only information on the log was this:

DETAIL:  Failed process was running: autovacuum: VACUUM
public.array_index_op_test

--
Jaime Casanova
Director de Servicios Profesionales
SYSTEMGUARDS - Consultores de PostgreSQL
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
set = {__val = {4194304, 140730796945184, 2, 6, 7058320, 
94276041338800, 4611686018427388799, 140054596995766, 0, 281470681751456, 0, 0, 
0, 0, 0, 0}}
pid = 
tid = 
ret = 
#1  0x7f60ffa21535 in __GI_abort () at abort.c:79
save_stage = 1
act = {__sigaction_handler = {sa_handler = 0x0, sa_sigaction = 0x0}, 
sa_mask = {__val = {0, 0, 0, 0, 0, 140054583844872, 2, 7305460047297970175, 
  7017846502343457077, 94276041338800, 7003723481572736816, 0, 
5992403885976025344, 140730796945424, 94276069437568, 140730796946288}}, 
  sa_flags = 1509191600, sa_restorer = 0x55be5ba13080}
sigs = {__val = {32, 0 }}
#2  0x55be5a654a30 in ExceptionalCondition (conditionName=0x55be5a8300e8 
"!IsPageLockHeld || (locktag->locktag_type == LOCKTAG_RELATION_EXTEND)", 
fileName=0x55be5a82ff63 "lock.c", lineNumber=894) at assert.c:66
No locals.
#3  0x55be5a46fd3a in LockAcquireExtended (locktag=0x7ffe71293470, 
lockmode=1, sessionLock=false, dontWait=false, reportMemoryError=true, 
locallockp=0x7ffe71293468) at lock.c:893
lockmethodid = 1
lockMethodTable = 0x55be5a9cf060 
localtag = {lock = {locktag_field1 = 68201, locktag_field2 = 2615, 
locktag_field3 = 0, locktag_field4 = 0, locktag_type = 0 '\000', 
locktag_lockmethodid = 1 '\001'}, mode = 1}
locallock = 0x55be5b95edb0
lock = 0x7ffe712934c0
proclock = 0x7ffe712934b0
found = false
owner = 0x55be5b97c368
hashcode = 2950126041
partitionLock = 0x7f60ffac75d1 <__GI___sysconf+929>
found_conflict = 116
log_lock = false
__func__ = "LockAcquireExtended"
#4  0x55be5a46d76c in LockRelationOid (relid=2615, lockmode=1) at lmgr.c:117
tag = {locktag_field1 = 68201, locktag_field2 = 2615, locktag_field3 = 
0, locktag_field4 = 0, locktag_type = 0 '\000', 
  locktag_lockmethodid = 1 '\001'}
locallock = 0x55be5b95edb0
res = 32766
#5  0x55be59f61698 in relation_open (relationId=2615, lockmode=1) at 
relation.c:56
r = 0x7f60ffa2b745 <__gconv_compare_alias_cache+117>
__func__ = "relation_open"
#6  0x55be5a00bb7a in table_open (relationId=2615, lockmode=1) at table.c:44
r = 0x7ffe71293570
#7  0x55be5a632160 in CatalogCacheInitializeCache (cache=0x55be5b9ab600) at 
catcache.c:925
relation = 0x7f60ffbbe330 <_nl_locale_file_list+16>
oldcxt = 0x53294b95ce457900
tupdesc = 0x7ffe71293630
i = 21950
__func__ = "CatalogCacheInitializeCache"
#8  0x55be5a632774 in SearchCatCacheInternal (cache=0x55be5b9ab600, 
nkeys=1, v1=94276069649056, v2=0, v3=0, v4=0) at catcache.c:1216
arguments = {140730796946976, 94276048942530, 94276069649088, 
1537494384}
hashValue = 15
hashIndex = 3
iter = {cur = 0x8, end = 0x55be5ba44d70}
bucket = 0x3712935f0
ct = 0x3f
#9  0x55be5a6325f1 in SearchCatCache (cache=0x55be5b9ab600, 
v1=94276069649056, v2=0, v3=0, v4=0) at catcache.c:1148
No locals.
#10 0x55be5a64dd15 in SearchSysCache (cacheId=35, key1=94276069649056, 
key2=0, key3=0, key4=0) at syscache.c:814
No locals.
#11 0x55be5a64e129 in GetSysCacheOid (cacheId=35, oidcol=1, 
key1=94276069649056, key2=0, key3=0, key4=0) at syscache.c:938
tuple = 0x55be5ba46aa0
isNull = 90
result = 21950
#12 0x55be5a07ba67 in get_namespace_oid (nspname=0x55be5ba46aa0 
"pg_catalog", missing_ok=true) at namespace.c:3090
oid = 32766
__func__ = "get_namespace_oid"
#13 0x55be5a07b773 in LookupExplicitNamespace (nspname=0x55be5ba46aa0 
"pg_catalog", missing_ok=true) at namespace.c:2954
namespaceId = 21950
aclresult = (unknown: 1537501856)
#14 0x55be5a07b123 in get_ts_config_oid (names=0x55be5ba46b28, 
missing_ok=true) at namespace.c:2736
schemaname = 0x55be5ba46aa0 "pg_catalog"
config_name = 0x55be5ba46ac0 "spanish"
namespaceId = 21950
cfgoid = 0
l = 0x55be5ba46ac0
__func__ = "get_ts_config_oid"
#15 0x55be5a64fe4e in check_default_text_search_config 
(newval=0x7ffe712939f0, extra=0x7ffe712939e8, source=PGC_S_FILE) at 
ts_cache.c:621
escontext = {type = T_ErrorSaveContext, error_occurred = false, 
details_wanted = false, error_data = 0x0}
cfgId = 3
buf = 0x55be5a687631  
"H\213E\260H\203\300\020\351\225\003"
namelist = 0x55be5ba46b28
 

Re: Assert while autovacuum was executing

[Peter Geoghegan]

On Sat, Jun 17, 2023 at 11:29 AM Jaime Casanova
 wrote:
> I have been testing 16beta1, last commit
> a14e75eb0b6a73821e0d66c0d407372ec8376105
> I just let sqlsmith do its magic before trying something else, and
> today I found a core with the attached backtrace.

The assertion that fails is the IsPageLockHeld assertion from commit 72e78d831a.

I think that this is kind of an odd assertion. It's also not justified
by any comments. Why invent this rule at all?

To be fair the use of page heavyweight locks in ginInsertCleanup() is
also odd. The only reason why ginInsertCleanup() uses page-level locks
here is to get the benefit of deadlock detection, and to be able to
hold the lock for a relatively long time if that proves necessary
(i.e., interruptibility). There are reasons to doubt that that's a
good design, but either way it seems fundamentally incompatible with
the rule enforced by the assertion.

-- 
Peter Geoghegan

Re: [PATCH] ltree hash functions

[Tomas Vondra]

On 6/17/23 20:19, Tom Lane wrote:
> Tomas Vondra  writes:
>> I guess the "correct" solution would be to extend ALTER OPERATOR. I
>> wonder why it's not supported - it's clearly an intentional decision
>> (per comment in AlterOperator). So what might break if this changes for
>> an existing operator?
> 
> This code was added by commit 321eed5f0.  The thread leading up to
> that commit is here:
> 
> https://www.postgresql.org/message-id/flat/3348985.V7xMLFDaJO%40dinodell
> 
> There are some nontrivial concerns in there about breaking the
> semantics of existing exclusion constraints, for instance.  I think
> we mostly rejected the concern about invalidation of cached plans
> as already-covered, but that wasn't the only problem.
> 
> However, I think we could largely ignore the issues if we restricted
> ALTER OPERATOR to only add commutator, negator, hashes, or merges
> properties to operators that lacked them before --- which'd be the
> primary if not only use-case anyway.  That direction can't break
> anything.
> 

Sound reasonable.

Tommy, are you interested in extending ALTER OPERATOR to allow this,
which would also allow fixing the ltree operator?


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Bypassing shared_buffers

[Greg Sabino Mullane]

On Thu, Jun 15, 2023 at 4:16 AM Vladimir Churyukin 
wrote:

> We're trying to see what is the worst performance in terms of I/O, i.e.
>> when the database just started up or the data/indexes being queried are not
>> cached at all.
>
>
You could create new tables that are copies of the existing ones (CREATE
TABLE foo as SELECT * FROM ...), create new indexes, and run a query on
those. Use schemas and search_path to keep the queries the same. No restart
needed! (just potentially lots of I/O, time, and disk space :) Don't forget
to do explain (analyze, buffers) to double check things.

deb’s pg_upgradecluster(1) vs streaming replication

[James Cloos]

Has anyone recently tried updating a streaming replication cluster using
debian’s pg_upgradecluster(1) on each node?

Did things work well?

My last attempt (11 to 13, as I recall) had issues and I had to drop and
re-install the db on the secondaries.

I'd like to avoid that this time...

Should I expect things to work easily?

-JimC
-- 
James Cloos  OpenPGP: 0x997A9F17ED7DAEA6

Re: lockup in parallel hash join on dikkop (freebsd 14.0-current)

[Tomas Vondra]

On 2/7/23 01:09, Thomas Munro wrote:
> On Tue, Feb 7, 2023 at 1:06 PM Tomas Vondra
>  wrote:
>> On 2/7/23 00:48, Thomas Munro wrote:
>>> On Tue, Feb 7, 2023 at 12:46 PM Tomas Vondra
>>>  wrote:
 No, I left the workload as it was for the first lockup, so `make check`
 runs everything as is up until the "join" test suite.
>>>
>>> Wait, shouldn't that be join_hash?
>>
>> No, because join_hash does not exist on 11 (it was added in 12). Also,
>> it actually locked up like this - that's the lockup I reported on 28/1.
> 
> Oh, good.  I had been trying to repro with 12 here and forgot that you
> were looking at 11...

FYI it happened again, on a regular run of regression tests (I gave up
on trying to reproduce this - after some initial hits I didn't hit it in
a couple thousand tries so I just added the machine back to buildfarm).

Anyway, same symptoms - lockup in join_hash on PG11, leader waiting on
WaitLatch and both workers waiting on BarrierArriveAndWait. I forgot
running gdb on the second worker will get it unstuck, so I haven't been
able to collect more info.

What else do you think would be useful to collect next time?

It's hard to draw conclusions due to the low probability of the issue,
but it's pretty weird this only ever happened on 11 so far.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company 1134  -  ICs   0:04.43 dhclient: genet0 (dhclient)
 1135  -  Is0:05.10 /sbin/devd
 1333  -  Is0:30.12 /usr/sbin/syslogd -s
 1433  -  Is0:13.19 sshd: /usr/sbin/sshd [listener] 0 of 10-100 
startups (sshd)
 1437  -  Is0:58.05 /usr/sbin/cron -s
54451  -  Is0:00.07 screen (screen-4.9.0)
59248  -  Ss9:52.76 /usr/sbin/ntpd -p /var/db/ntp/ntpd.pid -c 
/etc/ntp.conf -f /var/db/ntp/ntpd.drift
73926  -  I 0:00.01 cron: running job (cron)
73927  -  Is0:00.04 /usr/local/bin/bash ./run.sh
73929  -  I 0:01.98 perl ./run_branches.pl --run-all --verbose
73931  -  I 0:01.58 /usr/local/bin/perl ./run_build.pl --verbose 
--config ./build-farm.conf REL_11_STABLE
77639  -  I 0:00.02 sh -c { cd pgsql.build/src/test/regress && gmake 
NO_LOCALE=1 check; echo $? > 
/mnt/data/buildfarm/buildroot/REL_11_STABLE/dikkop.lastrun-logs/laststatus; } > 
/mnt/data/buildfarm/buildroot/REL_11_STABLE/dikkop.
77640  -  I 0:00.04 gmake NO_LOCALE=1 check
78893  -  I 0:00.33 ../../../src/test/regress/pg_regress 
--temp-instance=./tmp_check --inputdir=. --bindir= 
--temp-config=/tmp/buildfarm-VLvzXQ/bfextra.conf --no-locale --port=5718 
--dlpath=. --max-concurrent-tests=20 --port=5718
78902  -  I 1:18.47 postgres -D 
/mnt/data/buildfarm/buildroot/REL_11_STABLE/pgsql.build/src/test/regress/./tmp_check/data
 -F -c listen_addresses= -k /tmp/pg_regress-UyjmKQ
78905  -  Is0:01.14 postgres: checkpointer(postgres)
78906  -  Ss0:02.18 postgres: background writer(postgres)
78907  -  Ss0:02.57 postgres: walwriter(postgres)
78908  -  Is0:10.60 postgres: autovacuum launcher(postgres)
78909  -  Is0:41.88 postgres: stats collector(postgres)
78910  -  Is0:00.31 postgres: logical replication launcher(postgres)
79193  -  I 0:00.41 psql -X -a -q -d regression
79215  -  Is0:07.66 postgres: bsd regression [local] SELECT (postgres)
79301  -  Is0:00.08 postgres: parallel worker for PID 79215
(postgres)
79302  -  Is0:00.05 postgres: parallel worker for PID 79215
(postgres)
93662  -  Is0:00.14 sshd: bsd [priv] (sshd)
93664  -  S 0:00.06 sshd: bsd@pts/0 (sshd)
 1459 u0  Is+   0:00.27 /usr/libexec/getty 3wire ttyu0
 1451 v0  Is0:00.05 login [pam] (login)
 1460 v0  I+0:00.08 -sh (sh)


[Switching to LWP 100121 of process 79215]
_poll () at _poll.S:4
4   _poll.S: No such file or directory.
(gdb) bt
#0  _poll () at _poll.S:4
#1  0x884ccc60 in __thr_poll (fds=0x4, nfds=1, timeout=-1) at 
/usr/src/lib/libthr/thread/thr_syscalls.c:338
#2  0x00804e80 in WaitEventSetWaitBlock (set=0x9c945be0, 
cur_timeout=-1, occurred_events=0x814f4a28, nevents=1) at latch.c:1171
#3  WaitEventSetWait (set=set@entry=0x9c945be0, timeout=, 
timeout@entry=-1, occurred_events=, 
occurred_events@entry=0x814f4a28, nevents=nevents@entry=1, 
wait_event_info=, 
wait_event_info@entry=134217731) at latch.c:1000
#4  0x00804af0 in WaitLatchOrSocket (latch=0x9bc30d18, 
wakeEvents=wakeEvents@entry=1, sock=-1, timeout=-1, wait_event_info=134217731) 
at latch.c:385
#5  0x008049dc in WaitLatch (latch=0x4, wakeEvents=wakeEvents@entry=1, 
timeout=2286734372, wait_event_info=134217731) at latch.c:339
#6  0x006e3614 in gather_readnext (gatherstate=) at 
nodeGather.c:367
#7  gather_getnext (gatherstate=0x912f5e98) at nodeGather.c:256
#8  ExecGather (pstate=0x912f5e98) at nodeGather.c:207
#9  0x006f2da0 in ExecProcNode (node=0x912f5e98) at 
../../../src/

Re: [PATCH] hstore: Fix parsing on Mac OS X: isspace() is locale specific

[Michael Paquier]

On Sat, Jun 17, 2023 at 10:57:05AM -0400, Evan Jones wrote:
> However, if we think this change could be a problem, one fix would be to
> switch scanner_isspace() to array_isspace(), which returns true for these
> *six* ASCII characters. I am happy to submit a patch to do this.

The difference between scanner_isspace() and array_isspace() is that
the former matches with what scan.l stores as rules for whitespace
characters, but the latter works on values.  For hstore, we want the
latter, with something that works on values.  To keep the change
locale to hstore, I think that we should just introduce an
hstore_isspace() which is a copy of array_isspace.  That's a
duplication, sure, but I think that we may want to think harder about
\v in the flex scanner, and that's just a few extra lines for 
something that has not changed in 13 years for arrays.  That's also
easier to think about for stable branches.  If you can send a patch,
that helps a lot, for sure!

Worth noting that the array part has been changed in 2010, with
95cacd1, for the same reason as what you've proposed for hstore.
Thread is here, and it does not mention our flex rules, either:
https://www.postgresql.org/message-id/8f72262c-5694-4626-a87f-00604fb5e...@trumpet.io

Perhaps we could consider \v as a whitespace in the flex scanner
itself, but I am scared to do that in any stable branch.  Perhaps
we could consider that for HEAD in 17~?  That's a lot to work around
an old BSD bug that macOS has inherited, though.
--
Michael


signature.asc
Description: PGP signature