RE 1/x > 100
creating divide-by-zero failures where there should be none.
I'm not sure how we get around that; in general the planner
has little clue which operations can throw what errors.
regards, tom lane
> Are there scenarios
> where one can foresee needing to generate secrets not over ssl or a domain
> socket?
Windows users, who lack the Unix-domain option, would probably find it
quite annoying to be forced to use SSL for local connections.
regards, tom lane
point of fact a plain "cp" is generally not safe
enough. You need to fsync the written file, and on many filesystems you
also have to fsync the directory it's in.
> Generally for peace of mind folks use third party tools like:
+1. Rolling your own archive script is seldom advisable.
regards, tom lane
es the legwork and sends a patch ...
regards, tom lane
x27;re back to
square one.
Also, this is the kind of thing where version-to-version changes
in behavior will be REALLY user-unfriendly.
regards, tom lane
order to see the output as each index is
> checked?
If you're willing to code at the libpq level, see
https://www.postgresql.org/docs/current/libpq-single-row-mode.html
regards, tom lane
=UC/postgres}
...
That's what I get in a default installation. If I manually GRANT, it
changes to
2200 | public| 10 |
{postgres=UC/postgres,=UC/postgres,role_test=U/postgres}
What do you see?
regards, tom lane
aking, mixing PGDG RPMs with vendor-supplied Postgres RPMs
is a recipe for headaches. If you can drop the Red Hat Postgres RPMs
without causing dependency problems, do that. Otherwise, the
two-socket-files solution is probably the best.
regards, tom lane
ho ssh -l postgres -2 -C -p 2022 192.168.0.70 ${SHCMD@Q}
ssh -l postgres -2 -C -p 2022 192.168.0.70 'psql -h 127.0.0.1 -d test_db -p
5432 -c '\''CREATE EXTENSION "uuid-ossp" SCHEMA pg_catalog;'\'''
You could, of course, just use the end result of that -- but it's
probably better to have a shell script recalculate it on the fly
given the desired SQL command as input.
regards, tom lane
ack hasn't been taught about the INCLUDE feature. In
either case, you need to go consult pg_repack's author(s), who may or
may not keep up on this list.
regards, tom lane
stion is "how many expression evaluations am I
going to save over the life of the row, versus how many I pay up-front?"
You didn't address how many queries would benefit from having the index,
so the question is unanswerable with just these facts.
regards, tom lane
wo line endings not one.
I'd advise extracting the first hundred or so lines of the file and doing
a test import into a temporary table, just to verify the process.
regards, tom lane
:
Could you provide a self-contained test case for this? It's hard to
guess at what the problem might be.
regards, tom lane
tion read timeout). Is it possible to configure th read timeout for
> psql?
You could fool with your tcp timeout settings:
https://www.postgresql.org/docs/current/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SETTINGS
regards, tom lane
olling back when a transaction fails).
It seems like if you want to be this paranoid, you'd be better off
not exposing the variable to the GUC machinery in the first place.
You could use a custom set-function (like setseed) to replace the one
bit of functionality you do want.
regards, tom lane
Matthias Apitz writes:
> El día Mittwoch, Juni 17, 2020 a las 01:39:53 -0400, Tom Lane escribió:
>> Could you provide a self-contained test case for this? It's hard to
>> guess at what the problem might be.
> attached is a simple ESQL/C code;
> ...
> [6
retrieve a NULL.
regards, tom lane
n cheaper than updating it incrementally. For GIN indexes
specifically, see also
https://www.postgresql.org/docs/current/gin-tips.html
regards, tom lane
testing how many rows you can fetch without seeing the
error. Also "select * from mytab where ctid = '(m,n)'" is useful for
probing individual rows, once you get close to the problem area.
regards, tom lane
lly-indexable
subexpressions of WHERE clauses get matched to indexes. In the
example of "select distinct expression", the planner will never notice
that that expression has anything to do with an index.
regards, tom lane
ly
scan for something like "select expr from tab group by expr".
Not sure if that will move the needle enough to help for the
original problem.
regards, tom lane
ly
scan for something like "select expr from tab group by expr".
Not sure if that will move the needle enough to help for the
original problem.
regards, tom lane
you hold the original dblink session open throughout the function,
password changes after that session is opened won't matter. Why are you
opening new sessions? It's inefficient as well as introducing unnecessary
chances for failure.
regards, tom lane
espondent said, you need to describe what you're doing in far more
detail if you want useful comments.
regards, tom lane
ed --- but the connection
that was established before altering the password and user mapping
continued to work.
So, again: you really need to show us exactly what you are doing that
doesn't work. Because the details you've given so far do not lead
to an example that fails.
regards, tom lane
n what definition of "midnight" you want to use.
regards, tom lane
of materialized view mv1 | n
<<<<<
(14 rows)
where I marked the actually-interesting dependencies with <<<<<.
regards, tom lane
he update (because just doing it in one go will about double
the table's size-on-disk) and vacuum between the stages to reclaim disk
space. But that will take more time not less ... and again, on any
modern machine, such steps shouldn't be necessary for only 20M rows.
regards, tom lane
aybe (1) isn't a bug; but it sure seems like (3) and (4) are.
In any case, the documentation about this seems pretty inadequate.
regards, tom lane
atever you think about it in simple
mode. The non-simple modes are trying to pass the variable values
as extended-query-protocol parameters, and the backend is not going
to recognize $n inside a literal as being a parameter.
If we fixed (1) and (3) I think there wouldn't be any great need
to tighten up (2).
regards, tom lane
r by/group by/select-list entries
of the same query level, so this will definitely give you two different
evaluations of random().
regards, tom lane
xt and tried to do a newline conversion on it. If it was
passed through email that would be a really plausible mechanism...
regards, tom lane
Matthias Apitz writes:
> On Thursday, 25 June 2020 21:41:54 CEST, Tom Lane
> wrote:
>> regardless of the exact details, it seems like the most likely theory
>> about what is happening is that the dump file is corrupt and the
>> corruption is causing the de-gzipped
hould probably document that --- I'll go do so.
regards, tom lane
shortcoming
of the trusted-extensions feature AFAICS. It is not the intent of
that feature that you can randomly mark unsafe extensions as trusted
and have every one of their permissions safety-checks vanish.
regards, tom lane
"Brad Nicholson" writes:
> Tom Lane wrote on 2020/06/26 02:47:25 PM:
>> I'm confused about your point here. postgresql_fdw has intentionally
>> *not* been marked trusted. That's partly because it doesn't seem like
>> outside-the-database access is
aren't gonna be enough for (b) --- from memory,
that buffer is probably 8KB.
regards, tom lane
e will try again.
In short, yeah, you should just ignore these log messages.
regards, tom lane
we did not construct a PGresult then we would
need some other abstraction for access to the returned row, dealing with
error cases, etc etc. That would mean a lot of very duplicative API code
in libpq, and a painful bunch of adjustments in client code.
regards, tom lane
t you can run?
> You can run the query "SHOW segment_size" to show the compiled-in value.
pg_controldata will show it too, though a bit more opaquely:
Blocks per segment of large relation: 131072
This would be helpful if you have an on-disk database and no running
server.
regards, tom lane
st
from a quick eyeball: the array_agg() calls with no attempt to enforce a
particular aggregation order are concerning, and so is grouping by
a citext column (where you'll get some case-folding of a common value,
but who knows which).
regards, tom lane
tled "ENSOPC on a 10% used disk". It looks like the XFS crew
installed a fix for the underlying bug ... but that thread is from 2018
and you're running a 2015 Ubuntu release.
Or, to cut to the chase: maybe updating to a less hoary kernel would help.
If you are stuck with this Ubuntu release for some reason, consider using
a less bleeding-edge-at-the-time file system.
regards, tom lane
Anders Steinlein writes:
> On Thu, Jul 2, 2020 at 2:02 AM Tom Lane wrote:
>> I suspect the query underlying the matviews is less deterministic than
>> you think it is.
> Thanks for the tip, but I'm having a hard time thinking that's the case,
> seeing as I'
m; they all look like
they are references to JOIN output columns rather than the underlying
table columns or vice versa. That's probably harmless, but the different
join operators certainly are not.
regards, tom lane
Anders Steinlein writes:
> On Thu, Jul 2, 2020 at 5:43 PM Tom Lane wrote:
>> Unfortunately,
>> since the reverse-listing of this join is just going to say "USING
>> (email)", there's no way to detect from human-readable output that the
>> interpretation
them. Then it might be time for a table redesign.)
The decision-making about this is concentrated in
heap_toast_insert_or_update, which can be seen here:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/heap/heaptoast.c
regards, tom lane
27;t really have this sort of issue, since they're
just stored as text. Only a view or matview would preserve creation-time
decisions about interpretation.
regards, tom lane
ch climbs to 700ms per batch. A vacuum of
> the old table lowers is back to 150ms, but I don't understand why, because
> we structure the query to jump over all previously dead rows. There is an
> old thread in which Tom Lane mentions that the planner might itself be
> walking that
eted last, you'd never hit this behavior.
regards, tom lane
or the very last delete that
*is* handling the endmost ids.
regards, tom lane
Niels Jespersen writes:
> Sorry, "way to provide a default username" should have been "way to provide a
> default databasename"
Not sure, but you'd be more likely to find a knowledgeable answer
on the pgsql-jdbc list.
regards, tom lane
ck to plain \n.
Now the weird thing about that is I'd have expected "r" and "w" modes
to imply Windows text mode already, so that I'd have figured that
_setmode call to be a useless no-op. Apparently on some Windows libc
implementations, it's not. How was your installation built exactly?
regards, tom lane
r OS vendor.
regards, tom lane
depend to see if you
can identify any obviously-bogus rows.
No idea how it got that way. Have you had any database crashes or the
like?
regards, tom lane
gs during a DROP TABLE.
So my thoughts are now turning towards there being corruption
in pg_depend's indexes as well as the heap. Try doing the above
query after turning enable_indexscan and enable_bitmapscan off.
regards, tom lane
nts in v13 for the case of NOTIFY
with a lot of listeners [1]. Can't say of course whether that would
have fixed your problem, and v13 is still in beta anyway.
regards, tom lane
[1] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=51004c717
nt where you can expect that all operations will
be cheap even with thousands of partitions. We may never be at that
point, although people continue to chip away at the bottlenecks.
regards, tom lane
hoot what is causing the
> query to get stuck?
It's a very good bet that it's something about the query being
parameterized or not. You could try installing auto_explain to
compare the plans that are generated.
regards, tom lane
'm
wondering if the index is partial, for instance, though one would
think that the CTE form of the query wouldn't work either if so.)
regards, tom lane
#x27;s your disaster recovery plan in case the
primary fails? You need to have some good backup procedures in place.
regards, tom lane
in your automated process to
ensure that import_temp_2 has up-to-date stats before you try to do
this step. It seems somewhat likely that autovacuum takes care of
that for you in the "manual" case, but its reaction time is too slow
to fill the gap for the automated process.
regards, tom lane
tion.
But we prevent it from trying that on long IN lists, because it'd eat
lots of cycles and perhaps not be able to prove the desired partial index
qual anyway.
regards, tom lane
[1] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=65ce07e02
an a query
per row.
regards, tom lane
. (A word of
advice: if calling in outside help is even faintly possible,
don't do *anything* without first making a filesystem-level
backup that you can trust. Otherwise your own attempts might
just break things beyond the last chance of repair.)
regards, tom lane
and it had its share
of teething problems. I can't say whether updating to current
(9.5.22) would fix this particular issue, but it would definitely
fix a bunch of instabilities in that general area.
regards, tom lane
A user who can create such a cast can thereby
change the behavior of other people's queries.
regards, tom lane
=?windows-1250?Q?Thorsten_Sch=F6ning?= writes:
> Guten Tag Tom Lane,
> am Sonntag, 19. Juli 2020 um 20:37 schrieben Sie:
>> It's a security thing. A user who can create such a cast can thereby
>> change the behavior of other people's queries.
> I'm not sure
error). If you set -j to, say, half of -c then
each thread has exactly two connections to manage. If -j is too
small compared to -c then pgbench itself tends to become the bottleneck.
regards, tom lane
's no logic for that.
(Even if there was, it could not cope with circular references or
self-references.)
Best bet might be to not install the subscriber's foreign key
constraints till after the initial sync is done.
regards, tom lane
me of the data replication.
[ squint... ] Sorry, I completely misread the error condition.
Yeah, duplicate keys does seem odd here. Can you provide a self
contained example?
regards, tom lane
Thomas Kellerer writes:
> Tom Lane schrieb am 20.07.2020 um 20:04:
>> Yeah, duplicate keys does seem odd here. Can you provide a self
>> contained example?
> I'll try, but this is a production system.
> Extracting the necessary anonymous data will be tricky.
If this
sed to clear the "previous query"
buffer since there was nothing for it to remove from the "current query"
buffer. I argued then that that was confusing and counterproductive,
and I still think that.
regards, tom lane
u were wrong. The other join types
are notably less brittle.
regards, tom lane
information
about the pattern argument.)
regards, tom lane
timating one row, but that's as a result of different
decisions that I'm not nearly as willing to compromise on...)
regards, tom lane
hat has that property.
regards, tom lane
f this sort.
regards, tom lane
EXPLAIN ANALYZE for this privately. He
> wishes to keep the full output private.
So ... what was the *top* line, ie total cost estimate?
regards, tom lane
Andres Freund writes:
> On 2020-07-24 18:37:02 -0400, Tom Lane wrote:
>> Yeah. I'm fairly convinced that the v12 defaults are far too low,
>> because we are constantly seeing complaints of this sort.
> I think the issue is more that we need to take into accoutn that t
st of
the plan never gets executed, could we avoid compiling it? That is,
maybe JIT isn't JIT enough, and we should make compilation happen
at first use of an expression not during executor startup.
regards, tom lane
n this is true, JIT compilation will be performed."
?
regards, tom lane
t? Also possibly useful would be
a backtrace (set a breakpoint at errfinish):
https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend
regards, tom lane
If you can't manage to get back to that state, you might get somewhere
by waiting for the process to grow large and then attaching with gdb
and getting a stack trace. That's a bit less reliable than the
errfinish approach, though.
regards, tom lane
could be applied to compiled expressions,
but it's less obvious that you'd get enough matches to win.
regards, tom lane
e pg_dump
version, and diff the outputs to confirm).
regards, tom lane
ostgres backend. What
extensions are you using? (And what the devil would any of them
want with sqlite or libcurl? boost-thread is even scarier, because
we absolutely do not support multithreading in a backend process.)
regards, tom lane
ALTER USER ... SET ...).
regards, tom lane
7;reasonable' amount of time. I wonder what other folks do?
If you'd rather fail the migration process, sure.
regards, tom lane
keepalives ... don't know what options
it has for that.)
regards, tom lane
| 1.0 | pg_catalog | PL/PythonU untrusted procedural
> language
Hm. If you've been actively using plpython in this session, perhaps
libpython would have pulled all this other weirdness in with it.
regards, tom lane
velopers? Should I file
> a bug report? I'm using Postgres 12.2.
This is not a bug, and you should not hold your breath waiting
for it to change.
regards, tom lane
hema zoewang at character 15
Gonna need more context. The session-level user seems to have the
right privileges, but maybe something is happening inside a
security-definer function that doesn't have privileges?
regards, tom lane
Scott Ribe writes:
> On Aug 6, 2020, at 12:22 PM, Tom Lane wrote:
>> Gonna need more context. The session-level user seems to have the
>> right privileges, but maybe something is happening inside a
>> security-definer function that doesn't have privileges?
> The o
t any
session logging in as akanzler will immediately do "SET ROLE
confidential_read_only", after which it's the privileges of that
role not akanzler that determine what happens.
regards, tom lane
would
automatically do "SET ROLE confidential_read_only".
regards, tom lane
ster
children here but there might be others. On the other hand, if we do so
somebody will likely complain that they have a legit use-case for it.
regards, tom lane
e=verify-ca or sslosslmode=verify-full in your
connection string. See sslmode here:
https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS
regards, tom lane
memory" is a server crash rather than something
nicer, you probably need to corral your OOM killer. See
https://www.postgresql.org/docs/current/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT
regards, tom lane
a.
If you really need to have this proven to you, you can try "set
enable_hashjoin = off", but I don't think you'll find that better.
regards, tom lane
er you've got a buggy custom operator.
regards, tom lane
701 - 800 of 2962 matches
Mail list logo
