minutes by any user with the wits
to create a trojan-horse function or operator. Generally speaking,
you want admins to run with a minimal search path not a maximal one.
regards, tom lane
r there are no
bugs in this area.)
> Am I missing something, or is that PG's behavior?
I bet what you missed is granting (at least) USAGE on the
schema to that role. PG will silently ignore unreadable
schemas when computing the effective search path.
regards, tom lane
locking the optimization is
the casts inside the NIL macros. Still not our problem.)
regards, tom lane
ring all options.
Also, as David already mentioned, ANALYZE on pg_class might help.
regards, tom lane
application that finds this to be unacceptable should probably
not be using sequences.
regards, tom lane
PDATE-ing pg_database to use whichever spelling you think is
preferable; the strings appearing in datcollate and datctype aren't
stored anywhere else. (But experiment in a scratch installation to
verify that ... and don't try changing them to something that you
don't know to be semantically equivalent.)
regards, tom lane
on the net about what is the syntactic precedence in
FROM clauses.
regards, tom lane
TBH, I doubt it's worth worrying about.
regards, tom lane
informational?
This is checkpoint logging, which is on by default now (a decision
I didn't particularly approve of). Feel free to set
"log_checkpoints = off" if you don't want it.
regards, tom lane
here is that the *password* is not usable after
the specified date. If the user logs in via some non-password-based
authentication method, that's fine (and it's on the infrastructure
of that auth method to enforce whatever restrictions it thinks are
appropriate).
regards, tom lane
e, it caches that
in a hash table that it will never flush (which is arguably a bug for
other reasons, since those files aren't really immutable, but that's
how it behaves today). So you've got 0 chance of hitting this via
repeat SET TIMEZONE in a single backend.
regards, tom lane
Christophe Pettus writes:
> On May 13, 2024, at 11:17, Tom Lane wrote:
>> What's causing that I can't say. It doesn't look like we log the
>> errno anywhere when failing to read a zone file :-(
> File descriptor exhaustion? (Of course, that would mean somet
but this error is in a parallel worker process, which'd have
to read the file for itself during startup.
What's causing that I can't say. It doesn't look like we log the
errno anywhere when failing to read a zone file :-(
regards, tom lane
NIC, though.
> I set "log_min_error_statement = panic" according to the docs:
> To effectively turn off logging of failing statements, set this parameter to
> PANIC.
This setting controls whether the STATEMENT: detail is appended to a
message, but not the basic choice of whether to emit the messag
ccf96%40iki.fi
regards, tom lane
my_idx on t (cost=0.43..4.45 rows=1 width=8) (actual
time=0.001..0.002 rows=0 loops=1)
Index Cond: ((a >= 123450) AND (a = 0))
Heap Fetches: 0
Planning:
Buffers: shared hit=4
Planning Time: 0.081 ms
Execution Time: 0.013 ms
(7 rows)
For that, it's able to see that the index conditions are
contradictory, so it fetches no index pages whatever.
regards, tom lane
Dimitrios Apostolou writes:
> On Fri, 10 May 2024, Tom Lane wrote:
>> I'd say the blame lies with that (probably-default) estimate of
>> just 200 distinct rows. That means the planner expects to have
>> to read about 5% (10/200) of the tables to get the result, and
&
s to have
to read about 5% (10/200) of the tables to get the result, and
that's making fast-start plans look bad.
Possibly an explicit ANALYZE on the partitioned table would help.
regards, tom lane
may be filesystems out there that would give you guarantees in
this area, but it'd require non-POSIX and hence non-portable system
calls.
regards, tom lane
quot;days ago", even when days ago is zero?
> Explicitly casting "day to second" didn't work.
to_char() could be your friend here.
regards, tom lane
;t
read the docs closely enough to know the option exists, let alone
that it will help them. But maybe there's more use-case than
I'm thinking of.
regards, tom lane
"David G. Johnston" writes:
> On Friday, May 3, 2024, Tom Lane wrote:
>> By and large, I'd expect people using mixed-case table names to get
>> accustomed pretty quickly to the fact that they have to double-quote
>> those names in SQL. I don't see why
mes to get
accustomed pretty quickly to the fact that they have to double-quote
those names in SQL. I don't see why it's a surprise that that is also
true in \d commands.
regards, tom lane
y or correctness problem, but we do have dead
rows that must eventually get vacuumed away to prevent indefinite
storage bloat. That can be done by background housekeeping processes
though (a/k/a autovacuum).
I believe Oracle, for one, actually does use UNDO. I don't know
what they do about failure-to-UNDO.
regards, tom lane
s a reason why most Linux distros forbid static linking of
libraries from different sources.
regards, tom lane
orms. Different versions, different build options,
yadda yadda.
Maybe you should be thinking in terms of a docker container
or the like?
regards, tom lane
ch string, but
since it's been marked up to be a regex I fear that'd introduce
even more confusion than it solves.
regards, tom lane
ORDER BY 2, 3;
//
Did not find any relation named "public.some_idIds".
So it is in fact looking for public.some_idids.
regards, tom lane
t;Schema",
pg_catalog.format_type(t.oid, NULL) AS "Name",
t.typname AS "Internal name",
CASE WHEN t.typrelid != 0
THEN CAST('tuple' AS pg_catalog.text)
WHEN t.typlen < 0
THEN CAST('var' AS pg_catalog.text)
ELSE CAST(t.typlen A
nner is too aggressive about
applying JIT.
regards, tom lane
a column from
containing any OIDs that *weren't* large object OIDs. Given that
recording a large object OID elsewhere in the database is purely
an application decision, I don't think there's a reasonable way
for the system to track it.
regards, tom lane
your use-case. I believe my
example is pretty safe: neither datestyle nor timezone should
affect the timestamp-without-timezone variant of to_char(),
and this particular format string doesn't depend on lc_time.
regards, tom lane
USER
to reset the role's password.
regards, tom lane
> https://www.postgresql.org/docs/current/sql-altertable.html
> And none seem to involve the generated expression column. So the answer is
> no.
I think what you'd need to do is drop the existing column
and then add a generated column.
regards, tom lane
Marcos Pegoraro writes:
> But wouldn't it be good that VACUUM FULL uses that index defined by
> Cluster, if it exists ?
No ... what would be the difference then?
regards, tom lane
27;m surprised that's not noticeable in your
test case.
regards, tom lane
ll their commands. This might not even require malicious
intent, merely faulty coding --- but the opportunity for malicious
intent is staggeringly large.
regards, tom lane
em a bit wasteful. But it'll likely outperform any other solution
for the union queries. Notably, adding or deleting a partition would
be much less painful than redefining a UNION ALL view.
regards, tom lane
ity left
if you're letting a user that's not 100.00% trusted write event
triggers. (Much less execute any SQL command whatsoever, which
is what it sounds like David is suggesting you create a function
to do.)
regards, tom lane
ged thing
in Postgres. In particular, it does not grant you any powers over
installation-wide objects such as roles.
regards, tom lane
#x27;t
currently track when reasoning about ordering.
regards, tom lane
information we have that suggests any
plausible line of investigation towards a software problem.
If we can see a crash stack trace or two, maybe that would change.
regards, tom lane
e-ordinary
in the definitions. Or ... do you have any PG extensions installed?
regards, tom lane
ter's
log too (because it'd show up as the backend dying from SIGKILL).
regards, tom lane
with 128G RAM, but maybe
something is misconfigured to give up way below that. In any
case, you'll never find the cause from the client side.
> Last attempts have been with ubuntu 22.04.04 and postgreSQL 16.
16 dot which?
regards, tom lane
Thomas Kellerer writes:
> Tom Lane schrieb am 11.04.2024 um 01:02:
>> Jan Behrens writes:
>>> While writing a PostgreSQL client library for Lua supporting
>>> Pipelining (using PQsendQueryParams), I have been wondering if there
>>> are any single SQL comma
, but I want to continue afterwards".
But you can do that in other RDBMSes and probably somebody will be
motivated to make it possible in Postgres.
regards, tom lane
d its memory
> using numactl (or similar commands regarding cpuset) and partitioning.
Seems straightforward enough to me, as long as you put the instances
onto distinct port numbers. Are you encountering problems?
regards, tom lane
se we don't
do leap seconds.) The real number of distinct zones in a
standard tzdata file set these days is a shade under 600.
regards, tom lane
Adnan Dautovic writes:
> On 05. Apr 2024, at 16:13, Tom Lane wrote:
>> Out of curiosity, does
>> SET timezone to 'GMT';
>> work?
> Yes, it yields:
>> SET
>>
>> Query returned successfully in 84 msec.
I expected that, because the na
get in your face about it
until you did a postmaster restart.
regards, tom lane
did not do?:
> PG_CATALOG.PG_ROLES.ROLNAME%type
Exactly. The %type bit is important.
regards, tom lane
IME ZONE 'America/New_York'
or the like. See
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES
regards, tom lane
ich is a thin wrapper
over the wire-protocol Parse command). There is a different namespace
for cursors. Cursors do share that namespace with the wire-protocol
"portal" concept, but libpq doesn't directly expose portals.
regards, tom lane
same "portal" namespace.
regards, tom lane
e the NOTICE coming
out at unexpected times --- for instance, it might appear once
during the planner's constant-folding phase, even though naive
interpretation of the query suggests that it should appear many
times or not at all. But beyond that possible POLA violation,
neither of these functions will bother Postgres any.
regards, tom lane
u are trying to restore onto a different OS platform with
different locale naming conventions. The easiest way to deal with it
probably is to edit the dump file and change "C.UTF-8" to "C.UTF8"
everywhere. (Manually editing an 8G dump file might be no fun, but
"sed" should make short work of it.)
regards, tom lane
vidently
a fair number of entries in that have gone missing. Postgres
itself would never modify that data after installation, so
we're left to speculate about filesystem corruption or
somebody's odd desire to remove "unnecessary" files.
Out of curiosity, does
SET timezone to 'GMT';
work?
regards, tom lane
simplifies life by not requiring there to be different code paths
for EXPLAIN and normal query running in various places.
regards, tom lane
ted. If the remote's DBA is not willing to work on fixing
it, I suggest finding a job where you don't have to deal with that.
regards, tom lane
Fire Emerald writes:
> The partitioning must be the problem somehow.
[ shrug... ] You're still not providing any details that would
let somebody else reproduce or diagnose the problem.
https://wiki.postgresql.org/wiki/Guide_to_reporting_problems
regards, tom lane
e that all queries above succeed on Microsoft SQL Server.
SQL Server doesn't need to worry about an extensible type system.
regards, tom lane
sure that FK constraints are satisfied on-the-fly.
regards, tom lane
ts them", and that the reason your code is failing is
that there's still a reference to the PLyCursor somewhere after the
plpython function exits, perhaps in a Python global variable.
regards, tom lane
Don Seiler writes:
> On Tue, Mar 26, 2024 at 9:09 AM Tom Lane wrote:
>> ... So you could overshoot the specified target by
>> more or less the amount of WAL that could be emitted between two
>> checkpoints. Perhaps it's tighter nowadays, but I really doubt that
>&g
nts. Perhaps it's tighter nowadays, but I really doubt that
it's exact-to-the-kilobyte-at-all-times.
regards, tom lane
tial reaction is that we should warn only when the command
is a complete no-op, that is none of the mentioned privileges
matched. But I've not thought about it very hard.
regards, tom lane
ne should get notified of.
To do that, we'd have to remember that you'd said NULL, which we
don't: the word is just discarded as a noise clause. Considering
that this usage of NULL isn't even permitted by the SQL standard,
that seems like a bit too much work.
regards, tom lane
ume that what the planner does with a small table
is what it will do with a large table.)
regards, tom lane
CPU#25 stuck for 3121s! [migration/25:166]
Sounds like failing hardware to me :-(
regards, tom lane
with the
transaction's own existence. It can't conflict when acquired,
because the virtual XID is unique (at least across existing sessions).
It exists so that other sessions can wait for this one if needful, by
trying to take share lock on the virtualxid.
regards, tom lane
Adrian Klaver writes:
> Haven't had a chance to go through this yet. I'm going to say though
> that Tom Lane is looking for a shorter generic case that anyone could
> run on their system.
Yeah, it's a long way from that trigger function definition to a
workin
contained example that triggers this?
regards, tom lane
regards, tom lane
is already running (or not) for the current command, and
it's too late to change it with effect for that command.
regards, tom lane
ing
an awful lot of decimal places by the end of the query, I fear.
regards, tom lane
hassan rafi writes:
> The issue of high query planning time seems to intermittently resolve
> itself, only to reoccur after a few hours.
I wonder if you are running into the lack of this fix:
Author: Tom Lane
Branch: master Release: REL_16_BR [9c6ad5eaa] 2022-11-22 14:40:20 -0500
ven without that, it wouldn't be terribly practical
to insist on creating a separate copy of record_out (and every other
function that accepts composite types) just so that it could have a
hard-wired notion of what rowtype it's going to deal with.
regards, tom lane
te values.
I don't say that these problems are insoluble, but they do
look pretty difficult.
regards, tom lane
this rule is applied after constant-folding,
so that what we're left with is normally going to contain variables
at every level. extract() is problematic because it combines a
text constant with a datetime (collation-less) variable.
regards, tom lane
portant for performance.
regards, tom lane
uses.
Somebody ought to work on improving that mess sometime. One thought
that comes to mind is to have a server option authorizing postgres_fdw
to believe that all local collations exist on the remote side.
regards, tom lane
ver a "penny" is). Now,
that's still a Frickin Lot Of Money in any non-hyperinflated currency,
but it's the sort of restriction that banks don't like to hear of.
regards, tom lane
aurenz that it's hard to see much use-case here
that's not sufficiently covered already.
regards, tom lane
nse and CopyOutResponse messages don't carry any
column name or type indications, so yes it's normal.
https://www.postgresql.org/docs/current/protocol-message-formats.html
Maybe we missed a bet there, but AFAIR you are the first person to
ask for this in twenty-plus years.
regards, tom lane
t is about a table not existing or a table
column not existing. Maybe it's okay to let plpgsql_parse_wordtype
etc throw the error for themselves, though.
regards, tom lane
uld be explained if the users have
different search paths.
regards, tom lane
is not about the special characters
in the names, rather about search_path not including the NODE⠒V view.
Consider schema-qualifying the view name, or attaching a "SET
search_path" clause to the function.
regards, tom lane
* FROM users, x WHERE name = 'User '||trunc(r*100) ;
or in this case better to shove the whole constant computation
into the CTE.
regards, tom lane
operator. But
it hasn't gotten further than preliminary discussion.
For the moment, I think the only feasible solution is for your trigger
function to set the search path it needs by adding a "SET search_path
= whatever" clause to the function's CREATE command.
regards, tom lane
gt; if removing the quotes from the schema owner.
I'd advise reading this section carefully:
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
Notably, the advice to "always quote a particular name or never quote
it" could be a useful rule for you here.
regards, tom lane
be
simplified to
VALUES(p_event_id, tstzrange(p_start_time,p_end_time)) ...
regards, tom lane
do this
sort of thing is one of the fundamental advantages that open-source
distros have over closed-source ones, so you shouldn't ignore it.
regards, tom lane
If the diff between the .out files isn't pretty self-explanatory,
you can try checking the git log for the "_1.out" file to see why it
was created.
regards, tom lane
people who're still using an older version
of the extension's SQL declarations. Those are likely to remain
there indefinitely.
regards, tom lane
in v16, the loOpen fails, returning -1, while
> it used to be OK.
> So this must be a bug fix of some kind I guess. Using INV_READ fixed the
> test.
Yup, see commit 55f480278
regards, tom lane
ompatibility
before making the change on your production database. There are
likely to be a few gotchas.
regards, tom lane
f you use a cursor, and that doesn't
always work well :-(
regards, tom lane
would give some false positives.)
Alternatively, change your PATH to put /usr/local/bin in front
of /usr/bin.
regards, tom lane
p, but maybe you could make that happen. In any
case, more eyeballs reviewing that patchset would be welcome.
regards, tom lane
101 - 200 of 1912 matches
Mail list logo