e wasn't any. Best bet probably
is to convert your plpython[2] functions to plpython3 before you
upgrade.
regards, tom lane
gers choose to support.
The packagers frequently don't have a lot of choice in the matter;
once their platform drops python2, they can't support plpython2.
regards, tom lane
Marcin Giedz writes:
> all DBs checked and no plpython(2u) is found except for plpython3u
I think you also need to make sure you've dropped the plpythonu
and plpython2u extensions in every database.
regards, tom lane
xtension --- that's quite possible if
this database has been brought forward from some pre-9.1 state.
Try looking in each database with
select * from pg_proc where probin like '%python2%';
regards, tom lane
lpython_validator(oid);
It'll be interesting to see if there are any dependencies.
regards, tom lane
just the
probin values.
regards, tom lane
Bruce Momjian writes:
> On Wed, Nov 18, 2020 at 03:25:56PM -0500, Tom Lane wrote:
>> Maybe pg_upgrade should print the actual function names, not just the
>> probin values.
> It is done that way so we don't overwhelm them with lots of function
> names, and they can fo
ic solution. The generic
problem is how to usefully identify some functions that have dangling
probin pointers. I don't want a solution that only works for the
plpython functions.
regards, tom lane
ks that SIGQUIT'ing random processes is a good
thing to do.
regards, tom lane
mplementation
of NO KEY, which might be confusing matters still more.)
regards, tom lane
semantics though. It seems clear
that the client chose to roll back the transaction; the server did not
do that.
regards, tom lane
a text array not a single text value --- so it needs to
look more like "where pt = ANY($1::text[])".
regards, tom lane
number of milliseconds. It seems like something must be
blocking the query. Have you tried looking into pg_locks on the
remote server while this query is running?
regards, tom lane
Mats Julian Olsen writes:
> On 25.11.2020 17:58, Tom Lane wrote:
>> ... Have you tried looking into pg_locks on the
>> remote server while this query is running?
> Thanks Tom, I'll try to spin up a regular Postgres instance on both rds
> and ec2 and see if that help
k with manually-issued multi-row INSERTs. (This needn't
preclude continuing to access the table with postgres_fdw when
that's suitable.)
regards, tom lane
Gustavsson Mikael writes:
> After applying the latest patch we have encountered a problem with the
> pg_notify queue.
What do you mean by "the latest patch", exactly?
regards, tom lane
uot;
> So I think the client is using scram-sha-256
No, what that says is that the server is going to insist on scram-sha-256.
If the client can't handle SCRAM, then a failure would be expected.
regards, tom lane
ror.
If you back off the pg_hba setting to md5, does it work?
regards, tom lane
p tables then it can't be run in a worker anyway.
regards, tom lane
d is to add "-h /tmp" to your command when
running as root. Eventually you'd want to try to not have
multiple postgres installations on the machine.
regards, tom lane
ble and then writing them to the table as being
> much more efficient.
That's nonsense as far as Postgres is concerned.
regards, tom lane
e. Maybe we should add it.
regards, tom lane
by pattern matching not hierarchy, ie you get everything
whose name starts with "measurement". Depending on your naming
conventions, that might be close enough.
It does seem like there might be reason to have a switch along
the lines of "--include-child-tables".
regards, tom lane
it
interacts with the existing rules for deriving collations within a
query. Does parse_collate.c go out the window the minute somebody
sets a non-empty collation setting?)
regards, tom lane
all the
child FKs successfully, but it actually hasn't. If you
drop the FK constraint altogether, and recreate it having
DEFERRABLE INITIALLY DEFERRED from the start, it should
work --- at least it does for me.
regards, tom lane
foreign key
> constraint "sales_detail_cust_id_order_ts_fkey"
That I think you're gonna have to live with. DETACH PARTITION isn't
something we can defer to end of transaction.
regards, tom lane
isn't a primary key, then I'm confused too. Can we see the
full declaration of the table?
regards, tom lane
Ken Tanzer writes:
> On Mon, Dec 7, 2020 at 8:16 PM Tom Lane wrote:
>> If foo.id is a primary key, it knows that the "group by" doesn't really
>> merge any rows of foo, so it lets you get away with that. I think this
>> is actually required by spec, but am t
y recalling
that they may only require this behavior for the one case of
the function being UNNEST(). I think it was our choice to allow
it to work like that for any set-returning function.
regards, tom lane
ed.
An even more direct way is to run "pg_config" from the package and
see what it prints.
regards, tom lane
diately waiting for the result.
regards, tom lane
nknown reason, and falling back to non-SSL
which also fails.
regards, tom lane
pg_hba.conf entry for host "nn.nnn.n.nnn", user "kalle", database
> "postgres", SSL off
It'd be useful to verify that that version of psql+libpq is actually
built with ssl support. Try
ldd /usr/pgsql-13/bin/psql
and then repeat "ldd" on whichever libpq.so is mentioned in the output.
regards, tom lane
ects. I'd kind of assumed
that that's an error, but it isn't. Some experimentation indicates
that the behavior in all cases except two objects is to convert any
non-array input to a one-element array, reducing the situation to the
two-array case.
regards, tom lane
s to
be nuked from orbit, because aside from this fundamental semantic
issue it's got horrible performance problems with large inheritance
trees (ie many partitions). We might finally get that done for v14
--- at least, there's a patch in the queue about it. In existing
releases, I recommend the CTE solution.
regards, tom lane
ion I can think of is that your copy of libpq.so
is broken. Maybe you should try redownloading/reinstalling v13.
regards, tom lane
Michael Lewis writes:
> On Fri, Dec 18, 2020 at 12:16 PM Tom Lane wrote:
>> Yeah, this is a fairly fundamental shortcoming in inheritance_planner():
>> it supposes that it can duplicate the whole query for each target table.
> Are there other examples of gotchas with thi
o, you can't use the same directory to hold old and new versions at the
same time. After you're done with the upgrade, you could move the new
data directory to be where the old one had been.
regards, tom lane
on. Anything older than 1.0.1 is considered
outright insecure these days.
regards, tom lane
MATERIALIZED
(SELECT year % 19, year / 100),
Q2(h) AS MATERIALIZED
(SELECT (c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30 FROM Q1),
...
SELECT make_date(year, easter_month, easter_day) FROM Q6;
But I'd bet lunch that that won't be faster for this example,
because there's a lot of overhead in CTEs.
regards, tom lane
Which leaves us with a bit of a problem
for getting consistent results cross-platform.
regards, tom lane
rmat(), no special casting is needed.
regards, tom lane
status = CONNECTION_MADE;
return PGRES_POLLING_WRITING;
}
that is, it decides the connection it has is good enough. This
is not OK if SSL should have been used.
regards, tom lane
the state machine to
do that.
Have you got any environment variables, service files, etc
that would affect libpq's behavior?
regards, tom lane
Stephen Frost writes:
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>> However: it is true (and undocumented, so we have at least a docs bug
>> to fix) that v12-and-later libpq will try for GSS encryption first,
>> and if it succeeds then it will not consider using SSL, regardle
Stephen Frost writes:
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>> In the meantime, I did spot a code path that would explain the symptoms:
>> pqsecure_open_gss() clears allow_ssl_try sooner than it oughta. If
>> gss_wrap_size_limit() failed for some reason, we'd abandon
l_try reset in pqsecure_open_gss is definitely
even more broken than I thought before. I think we need to rejigger the
state machine so that it doesn't do that at all, but instead skips SSL
establishment when GSS is already active via an explicit test, rather
than munging the state variables.
regards, tom lane
g we tacked on later.
An example here is that a trigram index will only tell you which rows
contain the same trigrams that the query string does. Whether they are
in the right order has to be verified by a recheck of the original query
operator applied to the real data.
regards, tom lane
es that lack any
statistics are unlikely to produce the same plans as queries on populated,
analyzed tables. See for instance the advice at
https://www.postgresql.org/docs/current/using-explain.html#USING-EXPLAIN-CAVEATS
regards, tom lane
Kyotaro Horiguchi writes:
> At Wed, 23 Dec 2020 17:34:05 -0500, Tom Lane wrote in
>> However, in the tests Mikael ran after backing that pg_hba.conf entry
>> off to just "host", pg_hba wouldn't have caused an authentication-stage
>> failure, so it's
ake the
existing field say one of three things: "GSS encryption", "SSL
encryption", or "no encryption". As per attached. In the back branches,
it might be best to spell these as "GSS encryption", "SSL on", and "SSL
off", just to minimiz
7;) ;
> CREATE TYPE
> ...
> psql:/tmp/psql_tmp.5133.sql:16: ERROR: type "staffadmin.staff_role" does
> not exist
> LINE 9: role staffadmin.staff_role NOT null ,
> ^
Uh, you didn't spell it that way before.
regards, tom lane
n is defined like this, should we rebuild
the stats?
If you don't want to wait around for auto-analyze to do it, yes.
regards, tom lane
use IS DISTINCT FROM, to get sane behavior when one or
the other value is NULL.
regards, tom lane
encrypted-connection
setup to switch to SSL encryption, when we have a perfectly good
encrypted connection already.
regards, tom lane
PS: AFAICS, it's also undocumented useless damfool nannyism.
umentation
needs work, but this should be enough to solve Mikael's problem
if he's in a position to apply the patch locally.
regards, tom lane
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index 7d04d3664e..ec7c4c5e87 100644
--- a/
is;
for example, a message incorporating three or four source tokens
might not fit on one line anymore.
In short, I think you should take your complaint to the authors
of whichever driver or application you're using. They really
ought to try at least as hard as libpq+psql do.
regards, tom lane
riction I griped of earlier about
which auth methods can be used on a GSS-encrypted connection.
I made that a separate patch just in case it's controversial.
Finally, 0004 tries to improve the documentation in this area.
Some of that is reflective of 0003, but most of it is just
cleanup.
I pro
Svensson Peter writes:
> Yes, libpq patch solved our problem.
Thanks for confirming! I've pushed that patch and the server-side
fixes, so it'll be in February's releases.
regards, tom lane
e needn't explain what happens if you do.
regards, tom lane
diff --git a/src/backend/libpq/auth.c b/src/backend/libpq/auth.c
index 515ae95fe1..681448da2c 100644
--- a/src/backend/libpq/auth.c
+++ b/src/backend/libpq/auth.c
@@ -1054,29 +1054,18 @@ pg_GSS_recvauth(Port *port
ll,
not to psql. It's not real clear how that would happen given what you
show as your initial shell command, but maybe ${CSVPATH} contains
something odd? Also, as David noted, "-c" without any argument
certainly isn't right.
regards, tom lane
Stephen Frost writes:
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>> I think we'd be best off to always override KRB5_KTNAME if we have a
>> nonempty krb_server_keyfile setting, so the attached proposed patch
>> makes both functions do it the same way. (I did not make
--- is simply
not going to be that. Marking such a function immutable to try to end-run
around the restriction is unsafe.
regards, tom lane
the principle
applies to database query optimizers too.)
regards, tom lane
original SQL
text* listed the column as an update target. You can't tell
this way whether some earlier trigger changed the column's value.
regards, tom lane
d be a
different AM. btree is built for scalar values and has none of the
mechanisms that GIN does for breaking down input values into
components-to-be-indexed, nor for analyzing complex query operators
to find out what indexable search conditions are implied.
regards, tom lane
ge
values of that, then you could use tablespaces as a workaround.
But TBH you'd be better off moving onto a more modern platform.)
regards, tom lane
erating as designed. There might be scope for a feature
improvement here, but it'd be a far-from-trivial task, with probably
a lot of ensuing compatibility breakage.
regards, tom lane
[1] https://www.postgresql.org/docs/current/sql-createtrigger.html
t hardware ...) but you're
best advised to not rely on that, but think of Datum as a
physically distinct type.
regards, tom lane
h includes a fix for this [1] as well as hundreds of
far-more-significant bugs?
regards, tom lane
[1]
https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=d8ec6b9c8c265c2f29b1c0e0e4205895baaa326d
ther than 9.6.later. Read the release notes [1].
Insisting on 9.6.0 four years later is professional malpractice.
(I'll not even get into the question of whether you should be installing
some later release branch than 9.6.x; but I can't help noting that 9.6's
EOL is 11 months away.)
"Markhof, Ingolf" writes:
> I want the SLQ code of my views stored as I entered it. Is there any way to
> achieve this?
No. Lots of people prefer to keep their SQL code in some sort of
source-code-control system, anyway.
regards, tom lane
AFAIR, there hasn't been a lot of
push-back about those catalog columns disappearing.) So I don't
think we'd accept a patch to store the text form of a view, unless
some solution to these issues were provided.
regards, tom lane
des, rather than a per-module function.
regards, tom lane
compiled with --enable-cassert flag. It
> does lot of checks of memory corruptions
Yeah, you should absolutely use --enable-cassert when working on C code.
If you need valgrind, there's advice about how to run it at
https://wiki.postgresql.org/wiki/Valgrind
regards, tom lane
t you mean by "corrupted". It seems highly unlikely that any
code but your own is touching this memory.
Really the big-picture question here is what are you hoping to accomplish
and why do you think this memory might need to be shared?
regards, tom lane
do not
care which partition any particular row goes to.
Personally, I think hash partitioning is mostly academic, precisely
because of that. If the partitioning doesn't line up with application
requirements, you give up too much of the benefit of using partitions.
regards, tom lane
ight, even within a CASE expression.
regards, tom lane
ur and where they're visible
is going to be almost impossible to keep track of.
regards, tom lane
r cases you show work as they do because the
code for excluding irrelevant range-based partitions is able to
conclude that no partition need be scanned. That is, the
constant-false-one-time-filter plan arises when we have no
partitions remaining to scan, not because the plan for any one
partition would have looked different from what's above.
regards, tom lane
itioning
constraints. This is a heuristic based on the typical payoff of
excluding whole partitions versus skipping an empty index scan.
But if you have a workload where it's really worth spending
planner cycles looking for self-contradictory queries, you can
turn it on.
regards, tom lane
ecause then people are
forced to scroll through a whole lot of stuff to see what you added.
I see way too many people doing that lately, and to be honest I usually
stop reading their messages once I see that that's what they did.
You should only quote enough to remind the reader of what you're
responding to.
regards, tom lane
don't require looking at external
resources.
regards, tom lane
ould be a great deal
more expensive for large plans.)
regards, tom lane
e
and (b) circularity concerns --- the latter meaning that the PL's own
infrastructure is likely to expect that it can manipulate scalar values
without incurring recursion. These are pretty much the same reasons
why a datatype's I/O functions can't be written in a PL.
regards, tom lane
> Does that also go towards temp_file_limit?
No, a "temp file" is distinct from a temporary relation. Temp files
are used for anonymous transient stuff like a hash join's hash table.
> When are temp files (temp_file_limit) cleared out?
At the end of the query that used them.
regards, tom lane
Keith Christian writes:
> Planning to convert a Postgres 10 instance to systemd init. I understand it
> had to be compiled with systemd support to work that way.
Try "pg_config --configure" and see if --with-systemd is mentioned.
regards, tom lane
unwarranted assumptions about what
search_path it's invoked under. You could try schema-qualifying the
reference to f_validanumero_cnpj_cpf, or adding an explicit "SET
search_path" clause to f_testacnpjcpf().
regards, tom lane
is is unlikely to work well.
regards, tom lane
abase
overrides per-user. Look into pg_db_role_setting to see what
there is.
regards, tom lane
erate a scan path
at all, but that's likely to lead to an unintelligible error message.)
Perhaps you should rethink whether you really want a foreign table
rather than a set-returning function. With the SRF approach it's
automatic that the user must supply the restricting argument(s) you need.
regards, tom lane
i/Slow_Query_Questions
These sorts of questions seldom have generic fixes, but if you give
us enough detail, we might be able to help.
regards, tom lane
lays, and capture a stack trace from it, that would be
super-informative. Not sure about a good way to do that though.
Maybe you could automate tailing the log for "DETAIL: Process holding the
lock: nnn." and gdb'ing that process.
regards, tom lane
aws here...)
wraparound of the file names back to ?
regards, tom lane
0x80
This log entry shows that your query text and parameters all made it to
the backend just fine. So I don't think your issue is on the client side.
I'm wondering if the error could be from (say) triggers doing fancy data
manipulations. Noting your use of ON UPDATE CASCADE, it'd be worth
checking indirectly-affected tables as well.
regards, tom lane
w much can be recovered.
As with all else computer-related, there's no substitute for a
good backup plan :-(
regards, tom lane
Matt Zagrabelny writes:
> On Mon, Feb 1, 2021 at 5:57 PM Rob Sargent wrote:
>> You got one null from count(*) likely.
> What is count(*) counting then? I thought it was rows.
Yeah, but count(id) only counts rows where id isn't null.
regards, tom lane
o translate
a localized error message to the client's encoding. This could reflect
an incorrectly-encoded .po file, for example. So what we need to know
next is what lc_messages setting Jiří is using, and also the server
and client encodings.
regards, tom lane
ace
from the call to errfinish?
https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend
regards, tom lane
e in fear of ignoring
a few errors. pg_dump builds the script to be resistant to certain
types of issues, and missing tablespaces is one of those.
I do recommend capturing the stderr output and checking through it
to ensure you didn't have any unexpected errors.
regards, tom lane
901 - 1000 of 2935 matches
Mail list logo
