nary stuff)
The GRANT CONNECT will be in the archive, but pg_restore should only
print it if you specified -C.
Experimenting, however, I see that that only works as intended if
I use v11 pg_restore. I can replicate your result if I use v10
pg_restore. So ... don't do that.
regards, tom lane
row as one might expect.
Perhaps somebody ought to fix that, but it's such a corner case that
no one has bothered yet.
regards, tom lane
s of the storage hardware you'll need for this.
regards, tom lane
as opposed to just making the buffer bigger.
We have far too many GUCs already.
regards, tom lane
ce issues; if somebody can
show that that's not true, I wouldn't have any hesitation about
kicking it up.
(Might be worth malloc'ing it rather than having it as part of the
static process image if we do so, but that's a trivial change.)
regards, tom lane
Andres Freund writes:
> On 2019-07-27 18:34:50 -0400, Tom Lane wrote:
>> Yeah. The existing commentary about that is basically justifying 8K
>> as being large enough to avoid performance issues; if somebody can
>> show that that's not true, I wouldn't have any he
ixed without having a way to access and change
catalogs from a different database, which is a large can of worms
we don't want to open for such a small feature.
regards, tom lane
rse.
I think though that Adrian's point is a bit different: if you're not
allowed to see the original data, you should not be allowed to see
the statistics either. The histogram values and most-common-values
lists represent a pretty sizable information leak for such cases.
regards, tom lane
different installations. We don't want to artificially constrain what
people can use, so the range of allowed settings *ought* to include some
values that are not practically useful in specific situations. Those
values might be just the right ones for someone else.
regards, tom lane
release is flat out
folly.
regards, tom lane
;a=commitdiff&h=f946a4091
regards, tom lane
r variable-width cases is that
type_maximum_size() only knows about built-in types,
so the system can't determine the maximum row width.
regards, tom lane
an provide more info. But first - am I missing something obvious?
Yes: BRIN indexes don't provide any ordering information. A btree
index on created_at could be used to optimize this query, but without
one of those, seqscanning the whole table is the only possibility.
regards, tom lane
Jeremy Finzel writes:
> Thanks Tom. So, this is a very general question, but would it be possible
> to develop that feature into BRIN, given what it stores?
You'd need somebody who knows more about BRIN than me to opine on that.
regards, tom lane
which, in the worst case,
can take a long time. It's not obvious to me what you'd do with
BRIN.
regards, tom lane
le such that a uniqueness or
exclusion constraint disallows having multiple sets of data for the same
key. You'd still get failures and have to be willing to retry, but the
cause of the failure would be much more narrowly defined than it is
with a serialization failure.
regards, tom lane
an the latter.) You'd still need a trigger.
regards, tom lane
;s something that should be
created; the decision not to bother is probably older than the
fillfactor knob.
regards, tom lane
could probably get where you want to go with something along
the lines of
select id,
id_list,
next_list,
array(select unnest(id_list) intersect select unnest(next_list)) as
common_ids
from (
select id,
id_list,
lead(id_list) over (order by id) as next_list
from sample_data
) ss;
regards, tom lane
ritative on this; the symlinks are.
regards, tom lane
hem thinking
that the other one's lock files are relevant, and it provides an extra
layer of security against possible conflicts between the two instances.
regards, tom lane
nstraints to those
columns?
regards, tom lane
esting on a toy-size table, else the seqscan
cost estimate would be a lot higher. With a table large enough
to make it really important to guess right, even the default
selectivity estimate might be enough to get an indexscan.
regards, tom lane
> effectively blocks the use of GIN/GIST indexes when RLS is in use.
There's a whole lot of daylight between "it doesn't pick an indexscan in
this one example" and "it effectively blocks the use of GIN/GIST".
regards, tom lane
that's where you change it.
> Is there any documentation for which operators/functions are
> leakproof?
select oid::regprocedure from pg_proc where proleakproof;
regards, tom lane
dangling reference coming
from?". It's possibly hiding in the partitioning expression(s) of
this partitioned table, but there's no way to tell with this amount
of info.
How did you do the upgrade exactly?
regards, tom lane
ce about keeping some history is probably a better
plan though.
regards, tom lane
.* changing type from one
call to the next might have something to do with it.
I also wonder how often you create/delete child tables.
regards, tom lane
x27;t fix the
bad rowcount estimate per se.
regards, tom lane
e OR, that isn't possible.
regards, tom lane
f the query makes any sense at all.
regards, tom lane
e-property issue; the
role is marked as not being allowed to be used as a login role.
Probably because you said CREATE ROLE not CREATE USER.
You can fix it with ALTER ROLE ... LOGIN or something along
that line, check the ALTER ROLE page.
regards, tom lane
s user "postgres".
d2=# drop user joe;
ERROR: role "joe" cannot be dropped because some objects depend on it
DETAIL: owner of table tt
Unfortunately, we can't see into the catalogs of a different database
to explain about dependencies there ...
regards, tom lane
you've got more than one view named "subscribers",
in different schemas? If so this might not be the relevant entry.
regards, tom lane
) are allowed to connect as "employee".
regards, tom lane
pg_depend entries showing the type depends on
the functions.
regards, tom lane
"Johann 'Myrkraverk' Oskarsson" writes:
> On Tue, Aug 20, 2019 at 1:32 AM Tom Lane wrote:
>> You could manually update the pg_type row, and then if you were
>> being fussy, add pg_depend entries showing the type depends on
>> the functions.
> Can I
ee no reason to think that 3 and 5 would appear to be atomic.
regards, tom lane
n in all the August minor releases. Sorry about that :-(
regards, tom lane
O functions
seems unproblematic, but I'm not very sure what else is.
regards, tom lane
is happening.
regards, tom lane
created the
two functions manually rather than inside an extension update script,
so they're not known to be part of the extension. You could experiment
with ALTER EXTENSION ADD to see if this output changes when they are
part of the extension. (But you don't need ALTER EXTENSION ADD when
you create them in an update script.)
regards, tom lane
6548c9311b5777bf1f;hb=0ab7110bcbcce5ff58afb32e7871c54e87502139
FWIW, if you have both beta2 and beta3 executables at hand, it should
work to do a pg_upgrade to convert the cluster to beta3.
regards, tom lane
pends on your timezone setting. It would
be considered in-range in UTC+1 or further east (so that the date
wraps back to 294276AD); in or west of Greenwich, not so much.
> I tried to return a timestamp of the year 2000 to no avail.
Hardly ...
regards, tom lane
stan writes:
> Subject basically says it all.
A domain is a kind of type ... but it's not the only kind of type.
https://www.postgresql.org/docs/current/domains.html
regards, tom lane
d no side-effects but actually it did (maybe it
called a volatile function that did something), would you want those
effects to be persisted or not?
regards, tom lane
info() or a sibling to fill in the FmgrInfo.
regards, tom lane
Paul A Jungwirth writes:
> On Mon, Aug 26, 2019 at 8:12 AM Tom Lane wrote:
>> Yes, per the comment on those functions:
>> * These are for invocation of a specifically named function with a
>> * directly-computed parameter list. Note that neither arguments nor result
>&g
re we do know that.
On the other hand, nobody has ever complained about this before.
regards, tom lane
P-keepalive settings.
regards, tom lane
server can tell.
Perhaps there's monstrous network delays involved, but what I'm suspicious
of is inefficient client-side processing.
regards, tom lane
Hmm ... some weird DNS behavior, perhaps? That is one way to explain
a pattern like this. How long does it take to "dig" or "nslookup"
your server name?
regards, tom lane
in that the comments claim it
has something to do with the OLD row's id field(s) but the query is not in
fact taking that into account.
regards, tom lane
n
> these constraints?
As far as that goes, you'd just need to add parentheses.
regards, tom lane
al example:
> \copy(select count_value, sum(count_value)
> from table_name) to 'output_file.txt';
I think your problem is the line break, not the parentheses.
psql knows how to count parens, but it has no concept of letting
backslash commands continue across lines.
regards, tom lane
Rich Shepard writes:
> On Fri, 30 Aug 2019, Tom Lane wrote:
>> I think your problem is the line break, not the parentheses. psql knows
>> how to count parens, but it has no concept of letting backslash commands
>> continue across lines.
> Interesting. I've adopted se
urn a declared composite type.
Something like
CREATE TYPE two_dates AS (start date, stop date);
CREATE FUNCTION f(...) RETURNS two_dates AS ...;
SELECT * FROM f(...);
regards, tom lane
thmetic, like this:
$1 * '1 month'::interval - '1 day'::interval
It's faster (not enormously so, but measurably) thanks to not having
to construct and then parse a text string. To my mind it's easier
to reason about, too, and a bit safer.
regards, tom lane
. If we introduced URI-style escaping, we'd just make the
problem of funny characters worse, because then '%' would also become
magic. An alternative that doesn't risk breaking cases that work
today is to say that you can write '&&' to mean a literal '&', but
that seems kind of icky; there's nothing else that does that.
On the whole I'm content with the way the code works now, but we do
need to document it.
regards, tom lane
ECK_FOR_INTERRUPTS call before terminating. Perhaps there's someplace
we need to sprinkle a few more of those.
regards, tom lane
diff --git a/src/backend/executor/nodeResult.c b/src/backend/executor/nodeResult.c
index 80ed9cc..d09ac95 100644
--- a/src/backend/executor/
Will Storey writes:
> On Sun 2019-09-01 19:46:19 -0400, Tom Lane wrote:
>> A separate question is how come the particular query you're complaining
>> about has (seemingly) a fairly wide window where it never does any
>> CHECK_FOR_INTERRUPTS call before terminating.
the values specified in
the LIST clause, not values that perhaps could be shown to be related
to those values given extensive knowledge about the behaviors of
certain functions. By and large, the system doesn't have such
knowledge.
regards, tom lane
gt; Bitmap Index Scan on planet_osm_ways_nodes_idx
> (cost=0.00..11190.36 rows=1420982 width=0) (actual time=0.268..0.268
> rows=1 loops=1)
> Index Cond: (nodes && '{1}'::bigint[])
The planner should be able to do better than that, given up-to-date
statistics on the "nodes" column.
regards, tom lane
e sender immediately. Thank you.
I rather wonder whether I'm even allowed to read this, let alone
answer it. You do realize that this sort of add-on is completely
silly on a public mailing list?
regards, tom lane
becomes likely, and a postmaster restart is indicated.
BTW, you really ought to be running something newer than 9.6.2.
regards, tom lane
ot a lock on
the table the VACUUM is trying to process. The hypothetical offender
is probably somewhat old, or you'd not have noticed the blockage,
but we don't have any clear evidence as to how old.
regards, tom lane
le altogether. Is there any part
of your workflow that redefines the type of
product_owner.external_sys_class_code_pres.audit_update_dt or
product_owner.external_sys_class_code_pres ?
Also, you still didn't tell us the server's version.
regards, tom lane
eed to recreate an empty copy of this table (data can be
> recovered later), how can I achieve this?
TRUNCATE ought to be enough.
regards, tom lane
hing's been done about that either.
Disclaimer: in principle, doing this leaves you open to SQL-injection-like
attacks during the restore, if some malicious user has had access to
either the original source database or your restore target DB. That's
why we put in the search_path restriction. But it doesn't help to be
secure if you can't get your work done ...
regards, tom lane
tree, you'll have to recreate it ---
be careful to give the same configure arguments as you used before.
regards, tom lane
Moreno Andreo writes:
> Il 05/09/19 15:53, Tom Lane ha scritto:
>> TRUNCATE ought to be enough.
> ... or do I need to truncate the "master" table (the table blobs are in)?
Yeah, that one.
regards, tom lane
e the optimizer making invalid assumptions.
AFAIK "parallel safe" and "non-volatile" are more or less independent
restrictions, though someone might correct me. A function that writes
to the DB must be considered both volatile and parallel unsafe, but
if it doesn't do that then I think it could have any combination
of these properties.
regards, tom lane
; min(star date) and max(end date) for ALL the records for that given
> project.
Maybe I'm misunderstanding something, but isn't this just
select project_key, min(start_date), max(end_date)
from my_table
group by project_key
??
regards, tom lane
.
It's unlikely anybody will entertain an argument to undo it,
though, because (a) Ryu is faster than the old code, and (b) it's
hard to argue against ensuring that output/input preserves values.
You might consider using type "numeric" if you find truncation
of low-order zeroes undesirable.
regards, tom lane
ling with a wrapper
that is trying to figure out which version of psql to invoke.
regards, tom lane
ly. It's tricky since double-quote is
special to both the shell and SQL. You need something like
/usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d "$DBLIST" -c "GRANT
CONNECT ON DATABASE \"$DBLIST\" TO cpupdate"
regards, tom lane
a says "utf8". It's not apparent to me what relation
that might have to your observed problem, but it suggests that
this cluster doesn't exactly have a pristine history. Did you
perhaps try to move the data files from one platform to another?
regards, tom lane
lable
locales ... and there's no logic to flush it if /usr/share/locale
changes. The individual backends are probably inheriting the cache
state via fork from the postmaster.
regards, tom lane
n't know of any comparable issue in the core
tsvector logic, though. The numbers you're quoting do sound quite awful,
but I share Cory's suspicion that it's something about your setup rather
than an inherent Postgres issue.
regards, tom lane
"Kumar, Virendra" writes:
> Just got confirmation from developer that they are not modifying any type.
> We are on 11.5 RHEL.
Interesting. Can you exhibit a self-contained test case?
regards, tom lane
ult by dropping the
whole schema or database. But merely having create privilege doesn't
extend to that.
So basically you want a shared schema that is owned by some trusted
role, and your less-trusted roles have create (and usage!) on that
schema.
regards, tom lane
uot; does not exist
LINE 1: select * from (update t cross join update t2);
^
regards, tom lane
Adrian Klaver writes:
> On 9/12/19 6:44 AM, Tom Lane wrote:
>> No, it's *exactly* as if that. UPDATE is an unreserved
>> keyword so it's fully legitimate as a table name.
> I am not following.
Sure, the WITH thing works too. The point is that given
"SELECT .
ount
for accurately. Anyway I can't get excited about optimizing for
a single non-null value.
regards, tom lane
mplicated than cases like
it-must-be-a-2-character-state-abbreviation. You can use varchar(n)
if you must have an upper limit on the field length. Or text.
regards, tom lane
No, that's unsurprising given that you did a forced shutdown while
recovery from the initial crash was still running. The HINT is
probably not terribly well worded; it really only applies if
recovery crashed, which doesn't seem to be the case here.
regards, tom lane
rs did or might do --- you'd soon end up with circularities.
regards, tom lane
lunch,
especially if you don't trust your other triggers. (Although,
if you have so many triggers that that's a problem, I think you
might have some other design issues.)
regards, tom lane
ic enough --- it is carefully
*not* saying that the trigger will fire if the column changes value.
The CREATE TRIGGER man page never says that, either.
regards, tom lane
ou might try preprocessing
the data file before feeding it to COPY. Another way is to
copy into a temporary table that has very lax column data types
(all "text", perhaps) and then transform the data using
INSERT ... SELECT from the temp table to the final storage table.
regards, tom lane
the
PGXS infrastructure should get this right for you, but maybe
it isn't, or you missed out using it.
regards, tom lane
do see a small test in
src/test/regress/input/misc.source (COPY BINARY stud_emp),
but that solution doesn't scale easily because of its
dependence on absolute file pathnames.
regards, tom lane
path.
You could try doing EXPLAIN rather than running the query outright,
too, to see if you get identical plans. (Although if the performance
issue is down inside the function, that might not help much.)
regards, tom lane
Paul Jungwirth writes:
> On 9/18/19 7:26 AM, Tom Lane wrote:
>> Likely it'd be good to have some more consistent approach to
>> testing that ... right now it's not even very obvious where
>> is a good place to add such tests. I do see a small test in
>> src
it is already about as secure as
what you're doing now. If you want to jump through extra
hoops for more security, I think you can use ssh-agent to
hold the keys.
regards, tom lane
ing
> thing
> The number of "live " tuples went up..(?)
If you're looking at the pg_stat counter, that's only an approximation.
It's probably a better approximation now than it was before.
regards, tom lane
um then it's likely that they are all old.
Do NOT NOT NOT use VACUUM FULL here. It will not make things any
better than a plain VACUUM, and it will take a lot longer ... time
you maybe haven't got.
regards, tom lane
would be a win.
regards, tom lane
formance will get bad if the ACL
list gets too large. However, you can add lots of users to any group
role. So put the users into appropriate group(s) and issue database
permissions on the group level.
regards, tom lane
language
> (1 row)
> It does not.
Judging from the prompts you show, you installed the extension
in the "postgres" database, but you need it in the "stan" database.
regards, tom lane
301 - 400 of 2936 matches
Mail list logo
