AQ#Why_doesn.27t_PostgreSQL_report_a_column_not_found_error_when_using_the_wrong_name_in_a_subquery.3F
David J.
olate the subquery in the
manner you ascribe to them, you’ll just need to adapt to reality. Its
doesn’t have to seem logical to you, but this is how it is defined to work
and thus the observed behavior is not a bug.
David J.
contains two elements with the same value?
>
That is just how SQL works. A result set does not have all of the
characteristics of a formal mathematical set. Every produced row has a
unique identity independent of the value(s) of the fields. There are SQL
operations that can remove all but one of these identities from a result
set based upon the comparison of the field values (DISTINCT, UNION, etc...).
David J.
ard to say (I don't actually use the tool myself
though).
[1]
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-COMMENTS
[2]
https://docs.sqlalchemy.org/en/14/core/connections.html#sqlalchemy.engine.Connection.execute
David J.
rst things people do in their psql non-interactive automations (or run
them through the "tee" command...). It would be nice to not have to change
the pager but I also don't see messing with the default.
David J.
orks unless you want to report a
demonstrated bug.
You may wish to generalize a bit and just look at JSON and JSON Path
materially generally, we didn't invent this stuff.
David J.
;s not a good thing to do"
remains to be seen but at least for now we are just saying no to an
automatic rollback setting.
David J.
html
which CASCADE links to as well, may be an omission worth fixing (or please
point me to where this is covered…)
David J.
On Wednesday, April 6, 2022, m7o...@gmail.com wrote:
> David, thank you for the clarification.
> Should we consider raising log level for cascade drops from NOTICE to
> WARNING? By now cascade drops appears in log files only when log level >=
> NOTICE.
>
> --- a/src/backend
rop. I suppose
if we did have this kind of behavior we'd probably also have a way to
inform the system that, basically, there are no select privileges (or some
other spelling of "invalid") on the view, so any attempt to query the view
would fail even while the view still exists.
David J.
esn’t re-check the existing record, it effectively deletes it,
and then checks its replacement.
So, yes, the constraints were probably “put back”, but it was too late, the
invalid data was already saved.
David J.
meta-commands cannot be used.
Including a filename should establish the behavior you desire.
David J.
e done here is demonstrate
the documented behavior when the query buffer contains multiple commands
when \e is executed.
In either case this is working as documented and you really should be using
a permanent file for this kind of thing.
David J.
wn is wrong, but likely standard's
mandated). If it were a true cross join the relation u produced 4 unique
rows and the relation t produced 2, thus the output should have 8 rows. It
only has four. Because the lateral takes precedence here and only matches
a subset of the right-side output rows with the left side.
David J.
;m not aware of any documentation describing constraint evaluation order.
David J.
ss join sin(x)" as I dislike the implicit format even
more and the semantic mis-match with the cross join was unappealing as
well. There really is no other option for a LEFT JOIN here so just the
consistency with an INNER JOIN has now made writing "on true", at least for
a lateral join, make sense to me.
David J.
On Thu, Feb 17, 2022 at 9:32 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Thu, Feb 17, 2022 at 8:54 PM Tom Lane wrote:
>
>> "David G. Johnston" writes:
>> > On Thu, Feb 17, 2022 at 6:17 PM Tom Lane wrote:
>> >> There is no bu
On Tue, Apr 12, 2022 at 8:49 AM Tom Lane wrote:
> "David G. Johnston" writes:
> > - A function that's marked as dependent on an extension is
> automatically
> > - dropped when the extension is dropped.
> > + A function that's ma
_threshold_mono text text 'double precision'
You need to quote the fourth argument to protect the embedded space as a
character and not an argument separator.
David J.
atch the name and arguments at the same time when that isn't how the
meta-command is defined to be used.
David J.
On Fri, Apr 15, 2022 at 2:30 PM Rob Sargent wrote:
> On 4/15/22 15:18, David G. Johnston wrote:
>
> On Fri, Apr 15, 2022 at 2:04 PM Rob Sargent wrote:
>
>> but have not found a combination of name/args for
>> genome_threshold_mono(text,text,double
>> p
On Fri, Apr 15, 2022 at 3:02 PM Rob Sargent wrote:
> On 4/15/22 15:52, David G. Johnston wrote:
>
>
>
> On Fri, Apr 15, 2022 at 2:30 PM Rob Sargent wrote:
>
>> On 4/15/22 15:18, David G. Johnston wrote:
>>
>> On Fri, Apr 15, 2022 at 2:04 PM Rob Sargent
&
t to just forget doing a pg_upgrade migration and do a
pg_dumpall one instead. The missing template databases on the existing
cluster shouldn't be noticed.
You can decide how important missing the directories are to you, since that
shouldn't happen outside someone going in and "rm'ing" them.
David J.
very table in every database manually, you probably should
just do that. Vacuum freeze specifically.
David J.
ther the server should emit a notice or warning in this
situation is less clear. I'm doubting we would introduce an error at this
point but probably should have when parallelism was first added to the
system.
David J.
On Tue, Apr 19, 2022 at 7:47 PM Julien Rouhaud wrote:
>
> On Tue, Apr 19, 2022 at 07:21:19PM -0700, David G. Johnston wrote:
> > On Tue, Apr 19, 2022 at 7:07 PM Bryn Llewellyn
> wrote:
> >
>
> > > *alter function s1.f()security invokerset timezone =
l queries and output. Hopefully putting that
information together will cause you to realize where you are wrong. If not
we at least get something that is debuggable.
David J.
applied yesterday, at Tom's "Yeah, I arrived at
the same fix." email.
https://github.com/postgres/postgres/commit/344a225cb9d42f20df063e4d0e0d4559c5de7910
(I haven't figured out what the official way to reference a commit is, I
use the GitHub clone for research so there ya go).
David J.
On Wed, Apr 20, 2022 at 10:54 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:
>
>
> https://github.com/postgres/postgres/commit/344a225cb9d42f20df063e4d0e0d4559c5de7910
>
> (I haven't figured out what the official way to reference a commit is, I
> use the
On Tue, Apr 12, 2022 at 8:55 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Tue, Apr 12, 2022 at 8:49 AM Tom Lane wrote:
>
>> "David G. Johnston" writes:
>> > - A function that's marked as dependent on an extension is
>> auto
m freeze on the table) owned by the table owner and grant your
inserting process the ability to execute it.
David J.
to the slot even while the subscriber
is not active and the subscriber will receive all of it next time it comes
online/re-enables.
David J.
om three or four examples is pointless. And,
regardless, it isn't like any of those people are committers for the
project, whose opinions are really the only ones that matter because they
control whether to fix something or not.
>People seem to have been brainwashed by Web-Services and OLTP,
>and now think the working set must always fit in memory. But this
>is only one possible usecase, it is not the exclusive only one.
>
>
Again, your running commentary is providing zero, or negative, value here.
David J.
On Sat, Apr 23, 2022 at 1:58 PM Tom Lane wrote:
> "David G. Johnston" writes:
> > v12 what?
>
> That ...
>
> > It would help if you can provide a self-contained demonstration
> > that others can then verify and debug (or explain).
>
> ... and th
/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW
>
>
They've never executed a query. Probably due to connection pooling opening
connections for the pool but never needing to hand them out.
David J.
That makes sense to me. but I don't get the line *733
> to 734. *
> Also in the comment section, what does `quals + WCOs` mean?
>
>
WCO := With Check Option clause(the word option is implicit in the actual
clause name)
+ := And
Security Barrier Quals := Using clause
It is basically saying: "both RLS restriction types".
What don't you get about 733/734?
Inserting a row 2 (with valid dauthor value), row 2 exists so move to on
conflict update, row 2 is visible to the update because its cid is 11,
perform update since the new row will have a valid dauthor value.
David J.
,null,null,null);
ERROR: relation "people" does not exist
LINE 1: insert into people (person_nbr,lname,fname,job_title,company...
No syntax error, that it didn't find the table is expected.
Copied and pasted right from your email.
David J.
Here is the commit that brought the feature into existence (it includes a
link to the archives for discussion from which you can read or infer
things).
https://github.com/postgres/postgres/commit/f2fcad27d59c8e5c48f8fa0a96c8355e40f24273
David J.
ode it
yourself - possibly including ignoring the generic composite type
infrastructure and make a formal base type of whatever it is you need.
David J.
d of encoding
difference so the name you are typing in pg_dump and the name stored in the
database, while looking the same, are actually different? Copy-and-paste
the name from the pg_dump file back into the command line.
David J.
On Wed, Apr 27, 2022 at 4:16 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Wed, Apr 27, 2022 at 3:46 PM JORGE MALDONADO
> wrote:
>
>> Is this the correct way to answer when you say that I must *keep the
>> list cc'd*? I am not sure if I only
of multiple similar query tasks?
>
> Any example available?
>
>
You should search for how to run processes/commands in parallel in whatever
client-side execution environment you want to use. There isn't anything
specific to PostgreSQL here.
David J.
On Wed, Apr 27, 2022 at 4:44 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Wed, Apr 27, 2022 at 4:34 PM Shaozhong SHI
> wrote:
>
>> multiple similar query tasks are as follows:
>>
>> select * from a_table where country ='UK'
>> se
d that cycle and since the current
behavior matches the documentation, and there were/are no test cases for
this situation, the behavior change isn't surprising.
I'd accept a bug and backpatch solution here, though, if someone wished to
write one. We currently support a version (10) that allows this code to
execute, seemingly without issue.
David J.
in place from the query buffer. Upon returning you
are given a new buffer with the contents of whatever you typed into the
editor pasted in.
David J.
hat way myself).
In pl/pgsql you can also use variables, and the same goes for psql - though
that requires client involvement and so isn't generally that great a choice.
David J.
On Mon, May 2, 2022 at 4:24 PM Robert Stanford wrote:
> On Tue, 3 May 2022 at 08:39, David G. Johnston
> wrote:
>
>> You basically have to use "INSERT ... RETURNING" or variables. Which/how
>> depends on the language you are writing in. Pure SQL without client
&
in (player...) and (s.uid <> u.uid)
Hopefully you get the idea, your "social" dynamic makes this more
challenging. If you can just pass "my uid" into the function then figuring
out which uid is "me" and which is "not me" becomes quite a bit easier.
David J.
x27;ve just put player1 into the u1 slot.
> ...but how to bring the u1.muted or u2.muted there?
>
>
You can always write something like: CASE WHEN ... THEN u1.muted ELSE
u2.muted END if you don't want to pre-define "me" and "them"
David J.
On Wed, May 4, 2022 at 8:21 AM Alexander Farber
wrote:
> David, I am trying your suggestion:
>
> On Wed, May 4, 2022 at 4:27 PM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> Assuming the base query is capable of returning all related chat messages
>
On Wed, May 4, 2022 at 8:36 AM Alexander Farber
wrote:
> David, I try then the following -
>
> On Wed, May 4, 2022 at 5:28 PM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> You missed quoting the part where I describe the on clauses you need to
>&g
On Wed, May 4, 2022 at 8:53 AM Alexander Farber
wrote:
>
> JOINcte
> WHERE c.gid= in_gid
> AND (c.uid = myself.uid OR NOT opponent.muted)
> ORDER BY c.CREATED ASC;
>
> ERROR: syntax error at or near "WHERE"
> LINE 67: WHERE c.gid= in_gi
CREATED ASC;
>
> but the error is:
>
> ERROR: missing FROM-clause entry for table "myself"
> LINE 60: SELECT CASE WHEN player1 = myself.uid THEN play...
>
>
What exactly are you trying to do in the "opponent" cte - and why do you
think the myself cte is visible to it?
David J.
se.
You've defined (social,sid) as a primary key, your LIMIT 1 just makes you
look like you don't know or trust that and leaves the reader wondering.
Using (SELECT uid FROM myself) provides the same result without the
from/join reference; the usage in the case and the where clause could be
rewritten to use opponent.uid so myself.uid only appears once.
David J.
onent.uid <> in_uid)
>
> but still messages from the game #20 are displayed, even though I pass
> in_gid = 10
>
You want: gid and (uid or muted); what you have is: (gid and uid) or
muted; based upon operator precedence.
David J.
rs to application errors and then return control to the
calling application - which then needs to handle a clean return or an
application-level error return.
David J.
On Friday, May 6, 2022, Rama Krishnan wrote:
> Hi ,
>
> Can you pls tell us how to do a vertical partition in postgresql
>
Manually. “Create table” with the columns you want in each. You FK column
will also be your PK column on the non-primary table.
David J.
On Friday, May 6, 2022, Rama Krishnan wrote:
> Thanks a lot. Which means normal primary key and foreign key relationship
> right can u pls send me any reference link
>
Like what? Read the documentation for those commands and features.
David J.
s to manipulate it.
David J.
when OTHERS then
raise notice 'others - ok';
commit;
end;
David J.
On Saturday, May 7, 2022, Durumdara wrote:
>
>
> So is there any syntax to not fall on missing columns?
>
No. I’d probably approach this by generically converting the NEW record to
json and working with that. Non-existent object keys return null when
accessed.
David J.
mply the SQLSTATE for that name is perfectly clear to me and doesn’t
warrant the verbosity of the proposal to avoid.
David J.
6c6dff0f0eb428
The goal seemed to be able to accept 5-digit years…this behavior change
didn’t show in the tests (or discussion) though I didn’t look for the of
testing the pre-existing failure mode.
David J.
rom the documentation:
"Row-level BEFORE triggers fire immediately before a particular row is
operated on, while row-level AFTER triggers fire at the end of the
statement (but before any statement-level AFTER triggers)."
https://www.postgresql.org/docs/current/trigger-definition.html
David J.
ity of Data Changes" sections
> lets me see why the present restriction is needed.
>
>
I imagine having to keep around a working set of what are the changed
records is both memory intensive and also problematic should a future
statement make yet more changes to the table. This is also an area that
the SQL Standard does make rules in. And given that constraints are
defined per-row everywhere else there is a pull to not push the envelope of
our extension too far.
David J.
t it fires.
>
> To my surprise, it *is* legal to write code that accesses "old" and "new"
> values. But, because many rows can be affected by a single statement, and
> the trigger fires just once, the meanings of "old" and "new" are undefined.
> I've seen that, in any test that I do, both are always set to NULL (which
> seems reasonable).
>
I was thinking more about transition tables - though I admit it's not a
complete thought given their opt-in nature.
David J.
On Wednesday, May 11, 2022, David Gauthier wrote:
> Hi:
> psql (11.5, server 11.3) on linux
>
> I have a table with a bytea column which, of course, contains binary
> data. After 60 days, I no longer need the binary data but want to retain
> the rest of the record. Of course
On Wednesday, May 11, 2022, David Gauthier wrote:
> Doesn't vacuum run automatically (or can it be set to run automatically) ?
>
>
https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM
David J.
ll disclosure: I've never done this in anger.
>
> Try select * from pg_class or select * from pg_attribute or any of the
> other system catalogs.
>
>
Which is exactly what most GUI applications that provide object browsing
and viewing are going to use.
David J.
ant links/threads in the archives,
though I didn't try that hard.
David J.
a response here but they do
have their own list as well as documentation. It would be a purely UX
thing though, a user who wants to see the object in schema1 can choose to
do so manually.
David J.
d connect on the database directly. It
is through their mandatory membership in the PUBLIC pseudo-role, and that
role's default grant of connect on all newly created databases, that joe
receives permission to connect. You can only revoke what has been
explicitly granted so one must revoke the grant from PUBLIC - then
re-assign it to the subset of roles that require it.
https://www.postgresql.org/docs/current/ddl-priv.html
David J.
hrough PUBLIC. There is no privilege directly on Joe to revoke.
I don't quite know how to address your random experimentation with
pg_hba.conf. None of the things you showed are surprising though - were
you expecting different?
David J.
On Monday, May 16, 2022, Rama Krishnan wrote:
>
>
> How to install pg_repack and use?
>
Two questions best answered by reading its documentation.
David J.
p.s. please remember to include a subject line in future emails.
T *
FROM (...) AS temp (h)
JOIN my_srf_func(h) ON true
David J.
stgresql.org/docs/current/ddl-priv.html
Not saying there isn't room for improvement here, I tend to agree that the
SQL Command Reference section should be considered a primary jumping off
point. But the chapter on Data Definition is basically mandatory reading
once a DBA wants to do any non-tr
ld try creating a user function named "to_date" with (timestamptz,
text) as the signature and then inside the function cast the first argument
to text explicitly before invoking the built-in to_date(text, text)
function and returning its result.
You may have to deal with namespace/search_path issues though...
David J.
tion? And
> correspondingly from the CREATE accounts for the objects of the other types?
>
>
While I agree with the general premise that there is room for improvement
here, the degree of problem and manner of solution presented here doesn’t
seem like a specific solution I would endorse.
David J.
On Tuesday, May 17, 2022, David G. Johnston
wrote:
> On Tuesday, May 17, 2022, Bryn Llewellyn wrote:
>>
>> Might it be possible to give the paragraph more prominence (like make it
>> a note and start it with "WARNING" in large letters). And to x-ref it from
>
grant execute on function f() to public"? That would be good.
> But I can't find wording to that effect on the page.
>
No, the changes are to the defaults for the public schema - which makes
actually removing it from the database post-creation less necessary.
David J.
appear to be it, at first glance.
>
>
The server has no clue how the values sent to it came into existence - nor
should it.
Whether and how any particular client might expose this kind of debugging
information (or upgrade it to proper state info) is up to the client. I do
not know what option
le, *and that it returns nonzero
> status in this case*.
>
> Why exactly is this?
>
Newer doc versions also say:
This is an important safety feature to preserve the integrity of your
archive in case of administrator error (such as sending the output of two
different servers to the same archive directory).
David J.
nd "EXECUTE" command for ways to do this within the server. Or
use whatever client-side facilities you have at your disposal.
David J.
;
> ---
>
> My guess is that the following should be the same.
>
>
>
My reading of the docs say this is consistent with outcome #2.
https://www.postgresql.org/docs/current/collation.html
David J.
a is the
> same as the output of *show lc_collate*.
>
> so there is no *non-default? *
>
>
I’m not following the point you are trying to make. table111.a contributes
the default collation for any expression needing a collation implicitly
resolved.
David J.
On Wednesday, May 25, 2022, jian he wrote:
>
> I personally feel wording *non-default* may not be that correct. Because
> if the column is text then it automatically at least has default collation.
>
Non-default means “a value that is not the default value”.
David J.
s "just a string"?
>
>
>
Search_path isn’t a security component and accepts, but ignores, unknown
names. So yes, it is just a string.
David J.
te wrapper scripts they put into the version-agnostic bin
directory that deal with the version/cluster-name scheme they’ve setup
before calling the core commands located in the version-specific install
directory.
David J.
ontact the workspace administrator
> at Postgres for an invitation." The word "Postgres" is bolded. But it isn't
> a link.
>
> What must I do to join?
>
There is a web form for getting invited.
https://www.postgresql.org/community/
https://postgres-slack.herokuapp.com/
David J.
delete command should make
this self-evident.
David J.
On Thursday, May 26, 2022, Matthias Apitz wrote:
>
> Is there any way to get with the old CTID to the row, for example with
> the old CTID to the new one which the row now has after the update of the
> row?
>
>
No, there is no link between old and new in the main table.
David J.
are ranges. PostgreSQL has actual range types. Using them instead
of using text should make life considerably easier.
If you can go with PostgreSQL v14 you get access to multirange types.
Absent that you probably can use PostgreSQL array of ranges to accomplish a
similar goal.
David J.
four tables: books, authors, genres, and book_author_intersections. Then I
> scrunch each book back to a single JSON doc. I want to prove that I get
> back what I started with.
>
Yeah, the lack of any goal of round tripping conversions from JSON through
SQL and back into SQL makes proving that the system does such a thing
problematic. You'll get a back something meaningfully equivalent, by your
own argument, but not identical on a key-by-key basis.
David J.
s supposed to show up twice in the
output then the final solution is going to have to be a combination of this
and window functions. You need the later in order to be able to say "input
4, appearance 1" and "input 4, appearance 2" - which lead/lag tends to help
with. Then the group by becomes {input, appearance}.
David J.
logged in as "osuser" and
supply "-U postgres"; peer auth will work so long as osuser is mapped to
postgres and you connect via the socket (i.e., local, not host).
David J.
is specified.
>
>
>
+1
The lack of any explicitness pushes evaluation down to the base type -
which is a behavioral thing as opposed to some kind of attribute it
possesses.
David J.
columns, or the table's data type, as an input argument and put the
logic in there. Then just call the function in the policy with check
and/or using clauses.
David J.
instead of using IN.
If you want to return a useful count I'd move the delete into a CTE, add
RETURNING, count(*) the results, and return that (changing the function
output to either integer, text, or json as you desire).
David J.
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.
1201 - 1300 of 2416 matches
Mail list logo
