x27;)::kv])::kvarr);
ERROR: failed to find conversion function from kvarr to record[]
So the interaction of a composite type and the domain over array seems to
be the scope of the issue - which makes me thing bug even more.
David J.
non-conforming. COMMENT
ON table.column IS '@NULLABLE - optional information the customer might not
provide').
David J.
ot; shows "gk" with a "not null" constraint, whether I write
> it*
> *-- or not. And convention seems to say "don't clutter you code by writing
> it".*
> *create table genres(*
> * gk int primary key,*
> * gv text not null*
> * );*
>
"Primary Key" is defined to be the application of both UNIQUE and NOT NULL
constraints. It's not unlike saying "serial" to mean "integer with an
associated sequence and default". But let's not go there, please?
David J.
Index Only Scan that such a larger covering index is
supposed to facilitate.
David J.
ise attributed to said
mis-use and are on the user's head for allowing their code to produce the
problematic behavior.
At most we should probably go from saying "Best practice therefore..." to
"We no longer support setting a not null constraint on a domain but will
not error in th
strate the documented
behavior not working as documented (or not being functional even if
intended) to pinpoint any bug that might be lurking here. With only
fragments and statements that seem impossible we are left to assume
operator error. pg_dump is completely correct in what it is producing
(non-escape literal \000).
I also suggest using psql and pg_dump directly, and not pgAdmin, to
demonstrate a core PostgreSQL bug.
David J.
mation is
recorded either. So we don't know that the saved plan for f2() depends on
a specific version of f1() and thus if f1() is changed plans involving f2()
should be invalidated along with plans involving f1(). Nor is there
sufficient recognized benefit to doing so.
David J.
gt; artifact*.
>
That seems like too strong a position to take for me. Go ahead and build
immutable utility functions. Just don’t be stupid break your promise. But
even then, there are ways to fix things in case of bugs.
David J.
ocumentation does say
this though I lack the relevant paragraph reference at the moment.
David J.
le,
depends upon the value of "t".
Plausibly you might be able to produce something like:
select f2('DOG') => 'dog'
redefine f1(test); return 'cat'
select f2('DOG') => 'dog'
DISCARD ALL
select f2('DOG') => 'cat'
But in short extrapolating from a zero-argument scenario to a one-argument
scenario makes no sense. There are many more things to worry about, and
thus more reason to not optimize, when an input argument is involved.
Specifically, it is unlikely to be worth doing anything except within the
scope of a single query.
David J.
’t v14 still give that same error if some
client tried to present protocol version 1234.5680 as the desired
communicarion protocol?
David J.
ou indeed need to create a type that itself
is dynamic in terms of the information it stores (jsonb works) which also
involves throwing away the static typing of the outer layer and devolving
everything to basically text.
David J.
plus of
historical data that is out there probably generally unused. Setting up
partitions, or maybe even some separate archival database to be linked into
the new one via Foreign Data Wrappers, can also be considered.
David J.
to set it to a value where there are no additional
schemas added to the mandatory (and thus implied) < pg_temp, pg_catalog >
specification.
A bit off topic: I'm not sure how you came to the conclusion that
> superusers can't write into pg_catalog. They can.
True, but they need to jump through the hoop of modifying a setting before
they are allowed to do so.
David J.
replace all operator
invocations with their operator(schema.op) version without having to force
the view author to do so manually.
Being able to write: "SET search_path TO null;" instead of "SET
search_path TO '';" doesn't make my list. Now, "SET search_path TO
DEFAULT" maybe would work - if it helped solve the inlining limitation.
Have it go along with updating postgresql.conf to default to 'pg_catalog,
pg_temp'.
David J.
ut the value of an identifier anywhere you are expecting a plain
literal. But you cannot put a plain literal in places that are expecting
identifiers. SET is looking for literal values, which it stores, then
interprets as identifiers during use. A query doesn't "store" things for
later use so it looks for and requires actual identifier syntax (no single
quotes) - and more generally uses expressions which likewise care about the
difference.
David J.
or other GUCs and so this level of
imprecision is acceptable...I'm ok with coming away from this conversation
with the status quo but I'm not going to try and claim there isn't anything
confusing here.
David J.
ing that this advice is wrong (at least when a
> function will be invoked in more than a bare "select" because it prevents
> inlining.
>
> How should I resolve these two conflicting pieces of advice?
>
There is no "conflict" - you basically get to choose safety or
performance. Though since performance isn't guaranteed nor always a need I
would say choose safety unless you've confirmed that you need performance.
David J.
being able to properly use
search_path to achieve a goal the couple of paragraphs in the documentation
are sufficient for all practical purposes I can see. And do not, with any
frequency, seem to generate questions from our users.
David J.
quot; is the correct choice. No, rather "name" and
"identifier" in the context of database objects are known to mean the same
thing - the alphabetic name of the object.
David J.
l.
“Whitespace is allowed before and after the range value, but any whitespace
between the parentheses or brackets is taken as part of the lower or upper
bound value.”
David J.
increment in this situation.
Second, you need to figure out what the unique key in the provided data is
and key off of that for detecting duplicates. Ideally it already contains
some bigint key column which then makes the first point easy to accept.
David J.
s evaluating defaults so that every column has a value.
David J.
st case you wrote. Is this
another one of your mistakes in presenting a self-contained test case?
David J.
eded to make the output be a valid SQL identifier."
Change "object names" to "object identifiers".
Feel free to tug on that string if you'd like, I'm not convinced enough
that it is even incorrect and suspect that style of writing quite prevalent
in the docs. You'd need a larger sample size to argue with and propose
fixes for before a patch would likely be considered.
David J.
whole self-contained script
thing is very helpful.
You can also learn quite a bit by echoing queries in psql then using the
various description metacommands that query these same catalogs - thus
echoing the queries psql itself uses to answer this same question.
David J.
tions of the server since by leaving the supplied type info
undeclared the server can use its own logic. If it works for you I say use
it, it will be less problematic than methodically fixing your queries up
front. Though if there are some that show to be bottlenecks getting the
type info correct may prove to make a difference in some situations.
David J.
On Monday, July 18, 2022, Håvar Nøvik wrote:
>
> try
>execute transactional sql
> catch (commit failed)
>if (data is not stored)
> // regard data as not stored
Correct, the client did not get confirmation of commit success so it must
operate as if it failed.
David J.
s given that fact. If you try to cast the infinity to
numeric it will fail. If that doesn’t happen the query won’t fail.
David J.
that’s the correct application
> behaviour, just that I haven’t thought this through previously.
>
Right, since you sent commit there is now a non-zero chance the data is
committed but the client is unaware of that fact.
David J.
On Tuesday, July 19, 2022, Achilleas Mantzios
wrote:
> Thanks David
> Στις 19/7/22 17:19, ο/η David G. Johnston έγραψε:
>
> On Tuesday, July 19, 2022, Achilleas Mantzios <
> ach...@matrix.gatewaynet.com> wrote:
>
>>
>> ERROR: cannot convert infinity to num
too much.)
>
>
I'd expect that creating a "btree_gist_citext" extension would provide a
pathway forward here. IIUC, it could even depend on the two extensions it
is expanding upon.
David J.
.postgresql.org is basically impossible to inspect on the web.
David J.
On Wed, Jul 20, 2022 at 8:36 AM Adrian Klaver
wrote:
> On 7/20/22 07:57, David G. Johnston wrote:
> > On Wednesday, July 20, 2022, Marc Millas > <mailto:marc.mil...@mokadb.com>> wrote:
>
> > My general impression is that the packaging, at least for Debian,
>
On Wed, Jul 20, 2022 at 9:16 AM Adrian Klaver
wrote:
> On 7/20/22 08:55, David G. Johnston wrote:
>
> > That is what I found, and nowhere on the web can I find confirmation of
> > the existence of the package:
> >
> > 3.0.3+dfsg-2.pgdg+1
>
> https://apt.post
he OP seems to as well. Or maybe a functional subset so that
some number less than 35 may exist but, say, you cannot combine v14 and 3.0
since 3.0 since 3.2 was the most recent release of PostGIS when PostgreSQL
v14 came out.
In any case it does sound like the request by the OP is not something the
community has chosen to provide. Which means a choice on their part - move
up PostGIS or compile from source.
David J.
gt; > block at [1]). Wouldn’t it be better if the order of those constraints
> > were based on the table name?
> >
>
> Why does it matter?
>
>
As the code comment says:
/* To have a stable sort order, break ties for some object types */
This seems like it is simply a missed case.
David J.
, and 12:
>
>
>From the change to this page I'd say v14:
https://www.postgresql.org/docs/14/plpgsql-statements.html
The release note for v14 say:
PL/PgSQL:
Improve PL/pgSQL's expression and assignment parsing (Tom Lane)
This change allows assignment to array slices and nested record fields.
David J.
oes the server log have more info?
Can you reproduce if you run that query manually?
David J.
ash
command."
I don't see how you can do anything to make that work.
David J.
he product and still maintain access to the
> existing connections?
>
>
https://www.postgresql.org/support/versioning/
David J.
On Thursday, August 4, 2022, Danny Shemesh wrote:
>
> I believe the expressional index in itself could've been considered as
> covering, when querying for the expression explicitly.
>
This belief is wrong. When storing f(x) there is no way to recover the
value of x.
David J.
o do with pl/pgsql
specifically, but rather the runtime environment of triggers in the server,
it is not surprising the lack of discussion of this topic in that part of
the documentation.
David J.
m usually works, not because I
can point to a place where all that is said. I would for sure expect
deviations to be mentioned, and would find explicit documentation to be
reasonable to add if someone pushes forward such a change.
David J.
On Thu, Aug 11, 2022 at 1:19 PM Theofilos Theofovos
wrote:
> Hi subin,
>
> can you clarify please?
>
>
I got a random nonsensical reply from this sender earlier today
too...spam/hack/whatever, probably best to just ignore.
David J.
dozen
> words) makes comprehension quite hard—and structuring an account well-nigh
> impossible.
It encourages brevity so we consider it a feature ;) Usually, but not
here, the complaint is that it seems to do that too well...
David J.
p.s. just for testing I've included the original e
On Monday, August 15, 2022, Perry Smith wrote:
>
> I’ve been toying with row_number() and then sort by row_number descending
> and pick off the first row as the total number.
>
Use count as a window function.
David J.
uery, but the effects are identical).
q1 INNER JOIN LATERAL AS q2 ON true
Where q2 can use the columns of q1 in producing its output.
David J.
rovided by this community.
More general questions about Python programming might find better purchase
elsewhere.
David J.
t likely aren't actually doing that, and so the psql
output you show is meaningless since it doesn't show what the insert/select
command is actually working with.
If you are, showing the results of "select * from {tickets|tickets_archive}
limit 1" would be informative. Listing columns explicitly in the insert
command and then putting an explicit cast on
tickets_archive.ticket_purchase_no would also be interesting.
David J.
On Tue, Aug 23, 2022 at 5:12 PM Bryn Llewellyn wrote:
> *What do you all think?*
>
>
That bug reports should be sent to the -bugs mailing list with a succinct
test case demonstrating the bug.
David J.
;ll submit it to
> the psql-bugs list as you suggest.
>
>
Fair point, go ahead and just post to -bugs because I'm not going to be
diving that deeply into this right now.
David J.
line in
> PostgreSQL 12.5?
>
Your observation about the past isn’t going to change the answer
(especially if you choose to appeal to 8.2 behavior). It is a policy
choice, not a technical limitation.
David J.
; transition function.
>
This has nothing to do with the null. The null just happens to be the
third (ignoring the leading null) row and so this is the first time
“preceding 1” has resulted in a row being forgotten and its value removed
from the state. And it would add a new value but the transition function
is strict.
A moving aggregate function adds and removes individual values from its
state as the aggregate moves across the data. You provide functions to
handle both.
David J.
lternatively, you write a function with an exception block.
David J.
e database
will fail if invalid credentials are provided.
Or, you can use good credentials and psql to connect to a known database
and then query the system to learn, without an error, if other roles or
databases exist in the same cluster.
David J.
has a subchapter named "Authentication Problems" though it seems
like discussion of (typical) default peer dynamics is missing.
In particular, if you connect via local socket (also a default) the order
of entries in (typical default) pg_hba.conf says you must login using peer,
not password.
David J.
x27;t work consider a documentation patch if
you feel the typical DBA would remain under-informed even after reviewing
the documentation (though without a dedicate section discussing such best
practices I suspect such material would go unread by those who would most
need it).
David J.
12 | vagrant|
|
40 ||
|
466659 ||
|
42 ||
|
(8 rows)
postgres=> select pg_terminate_backend(470391);
ERROR: permission denied for function pg_terminate_backend
David J.
Or browsing the syntax chapter via the table of contents (everything is
either a function or syntax, and you ruled out the former).
https://www.postgresql.org/docs/current/sql-syntax.html
David J.
"pg_proc".
>
I do not see this 4.2.12 reference you speak of.
David J.
w with one column."
> On the other hand, an extra pair of surrounding parentheses here
>
> select array( (values (17), (42)) )
>
> while not necessary, *is* tolerated.
>
An actual subquery works here so the parentheses are grouping in nature and
not an inherent part of the syntax.
David J.
provide an
avenue I believe.
David J.
Which is the more interesting one since the ambiguous column name error you
did show has been resolved...
> What would be the proper format for the "on conflict" clause ?
>
I think that since your index is partial you need to modify your insert
command's on conflict clause to include a matching where clause. (WHERE
index_predicate)
https://www.postgresql.org/docs/current/sql-insert.html
David J.
ecifies an alternative action to raising
a unique violation or exclusion constraint violation error.
...
ON CONFLICT DO UPDATE updates the existing row that conflicts with the row
proposed for insertion as its alternative action.
https://www.postgresql.org/docs/current/sql-insert.html
Which seems sufficiently clearly written.
David J.
ntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
So I can see an argument for the existing behavior. It doesn't seem worth
changing in any case. And I don't really see the documentation being
improved by covering this corner case in detail when the current behavior
is at least intuitive.
David J.
ting
out that "if necessary" does not indeed match up with the behavior.
I suspect it is likely to get changed - everything else being discussed
just detracts attention from it.
David J.
>
> Yep, and that is precisely what would make for a good bug report. Pointing
> out that "if necessary" does not indeed match up with the behavior. I
> suspect it is likely to get changed - everything else being discussed just
> detracts attention from it.
>
>
> **B
;t exactly
considered desirable or useful to incorporate blank padding space into
data. Most of us just pretend char(n) doesn't exist. Frankly, varchar(n)
is the same - one can live a long and happy life with just text.
> How to remove p_namespace parameter from colwidth()? ColWidth() should
> return column width in first search_path table just like select ... from
> test finds table test.
>
Not sure on the full syntax but it probably involves doing something like:
table_name::regclass to get the OID and perform the lookup using that.
https://www.postgresql.org/docs/current/datatype-oid.html
David J.
he
server. You would need to execute ColWidth then write the returned value
of the function call into the text body of the SQL Command.
David J.
g
> current
> and reissue the query.
>
> Am I right?
>
Do it and find out? Then if still confused, show what you attempted. But
I don't know what this concept of "make the catalog current" you speak of
comes from. That isn't a thing that I am aware of. Where do you see this
documented?
David J.
e
> that current catalog and so that select would give me all tables from
> (finance_2021).
>
> I hope now its clearer.
>
No, it is not clearer. I suggest you show some code. Given what you've
said my guess is you are not correct.
David J.
ry output, nor able to be read or inferred by
some kind of side-effect. Your spy function, which is not leakproof,
should never see such prohibited rows - which you seem to have confirmed.
The planner indeed must ensure other security-related filters are applied
first.
David J.
al small enums, on
> millions of rows?
>
int2
David J.
p.s., pretend char doesn't even exist.
on expression
can refer to other columns in the table, but not other generated columns."
David J.
On Wed, Oct 19, 2022 at 12:34 PM Mark Raynsford
wrote:
> On 2022-10-19T11:58:07 -0700
> "David G. Johnston" wrote:
>
> > On Wed, Oct 19, 2022 at 10:36 AM Mark Raynsford <
> co+org.postgre...@io7m.com>
> > wrote:
> >
> > > insert into t (
picked it up "from the
source".
David J.
On Fri, Oct 21, 2022 at 6:09 PM Tom Lane wrote:
> "David G. Johnston" writes:
> > On Fri, Oct 21, 2022 at 4:52 PM Ravi Krishna
> wrote:
> >> on a diff note, is the word memoize inspired from Perl Module memoize
> >> which use to do the same thing.
>
, and {DEFAULT}.
Though if it contains DEFAULT and the VALUES is not part of an INSERT an
error should eventually occur during parse analysis since a plain VALUES
command has no context from which to retrieve a default.
David J.
cumentation as well:
https://www.postgresql.org/docs/current/queries-union.html
David J.
On Tue, Oct 25, 2022 at 8:22 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Tue, Oct 25, 2022 at 7:06 AM Rob Sargent wrote:
>
>>
>>
>> 2: select * from table join table b on Id = idb
>>
>>
>>
> #2 is probably conceptually
On Tue, Oct 25, 2022 at 8:56 AM Rob Sargent wrote:
> On 10/25/22 09:24, David G. Johnston wrote:
>
> On Tue, Oct 25, 2022 at 8:22 AM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Tue, Oct 25, 2022 at 7:06 AM Rob Sargent
>> wrote:
>>
hich I've seen
bandied about here a bit.
It isn't that special but if the bootstrap user name and o/s user name are
not the same name then you've broken an almost universal convention that
exists to make stuff like logging it with peer authentication work better.
David J.
ry staging table that
lacks the limit, then use insert to move the transformed data (via a select
query) into the production table.
David J.
t want to explicitly specify one you
better arrange things so the default you end up using is postgres, which
means your O/S user has to be postgres). Otherwise, as your "usr" example
demonstrates, just create a database named "usr" and you won't get the
"database usr not found" error message anymore and the login will succeed.
David J.
On Thu, Oct 27, 2022 at 3:24 PM Bryn Llewellyn wrote:
> *«*
> *You can start a session without specifying the name of the cluster role
> as which to authorize, its password, and the name of the database to which
> to connect, ONLY when these things are true:*
>
>
>
> *1. The within-cluster catalo
er's bootstrap superuser." Or just
consider a "cluster superuser" the term d'art...since most people would
just refer to any old role having superuser authorization as being plain
ole "superuser". The fact that is says "database superuser" is the same
holdover effect as the fact that "init db" means "init database" even
though it actually initializes a cluster.
David J.
On Thu, Oct 27, 2022 at 4:02 PM Tom Lane wrote:
> "David G. Johnston" writes:
> > Yes, the description for --username probably should be modified to read:
>
> > "Selects the user name of the cluster's bootstrap superuser."
>
> Yeah, perhaps.
t be accepted.
The mapping file simply allows the DBA to accept a role name that you
specify that, in the case of peer, is different than the o/s user name you
are physically establishing the connection over. In short, bob can request
to login as alice but the database is not going to peer authenticate that
request unless the pg_ident.conf file says that it ok for bob (o/s) to be
alice (system).
David J.
s not possible to make an alias mapping work without specifying "-U" on
the psql command line. Period. The -U is precisely how you tell the
server you are using an alias - without it the server expects that the o/s
user is logging in using their own name as the requested login role. In
that case either a peer entry for the user exists - and thus authentication
is successful - or it doesn't - and authentication will fail.
David J.
examples.
https://github.com/postgres/postgres/commit/66abc2608c7c00fcd449e00a9e23f13f02e65d04
David J.
at
> "dog$house" had not been created while "mac$crooge" HAD been.
>
>
Also note the "useradd" != "adduser" - you are running two different
commands. One of them is stock Linux while the other is provided by Ubuntu
(probably Debian, actually, too lazy to research specifics).
David J.
trap user be anything but postgres and this whole
thing is counter-productive. But if you are going down to first
principles maybe you should install from source and build your own
"package" from that.
David J.
and
configuration "main" to find out where certain things like the socket file
are being written to. The argument being the configuration files don't
actually contain secret data so reading shouldn't be an issue and can be
useful. Obviously the same does not apply to data files. On that basis it
would indeed make more sense to grant read to "all" rather than try and add
users to "postgres" to make the reading of the configuration files work.
David J.
On Tue, Nov 1, 2022 at 1:39 PM David G. Johnston
wrote:
> On Tue, Nov 1, 2022 at 1:20 PM Bryn Llewellyn wrote:
>
>>
>> All this leads to an obvious question:
>>
>> *«*
>> *Given that all of the config files have been made readable by "group"
&g
s it?
>
The teams responsible for packaging up source code and building
user-friendly installers for their operating systems are all different from
the core team that doesn't really have to worry about distribution. Aside
from the presence of, and commentary on, the relevant file configuration
variables.
https://www.postgresql.org/docs/current/runtime-config-file-locations.html
In short, it is actually a very good design given the constraint that the
software has to work on so many different operating systems for many years
of supported lifetime.
David J.
On Wed, Nov 2, 2022 at 6:22 PM David G. Johnston
wrote:
> Some repetition of what Adrian just posted ahead...
>
> On Wed, Nov 2, 2022 at 3:31 PM Bryn Llewellyn wrote:
>
>>
>> How can it be that the PG doc itself leads you by the hand to a regime
>> where you nee
you would need
to produce and show a self-contained test case to provide further help.
The information you have shown so far indeed looks problematic but
insufficient to diagnose further.
pg_dump -n schema is documented to dump both the schema and its objects so
pg_restore should be restoring both, and should be restoring schemas before
trying to restore most anything else.
You could try playing with pg_restore -L and try to get a sequence that
works.
David J.
under the
only semi-powerful database owner role you can probably bridge the gap (if
you indeed must have the database do things directly on the underlying
operating system).
David J.
7;t have to since PUBLIC gets that privilege and you didn't revoke
it.
https://www.postgresql.org/docs/current/ddl-priv.html
David J.
1301 - 1400 of 2437 matches
Mail list logo
