On Tuesday, November 7, 2023, jian he wrote:
>
>
> Why does the 2nd query fail? What am I missing?
>
>
The language specific implementation detail note on this page.
https://www.postgresql.org/docs/current/xfunc-sql.html
David J.
the
newly saved tuple. There is no leave-me-alone concept. You don’t get to
know if the column was specified or not in the update command.
David J.
given name and argument types. You
> might need to add explicit type casts.
>
We abbreviate with a ending “p”. regexp_*
David J.
isn’t an extension.
David J.
ll functions in the
database. All roles inherit the grants given to PUBLIC.
David J.
trying to
figure out what additional join conditions are needed so that only a single
matching row is found.
Your other table doesn’t have a primary key defined. If you do that the
answer will likely present itself to you.
David J.
d as well, that doesn't change the fact there
can be multiple attached queries to it.
The documentation explains clearly the implications on adding the keyword
RECURSIVE after the keyword WITH.
https://www.postgresql.org/docs/current/sql-select.html#SQL-WITH
David J.
less your query is insanely large this benefit seems marginal.
> Sorry for the question but I'm not entirely sure how stored procedures and
> prepared statements work together.
They don't.
David J.
tions will make itself clear.
David J.
s and uses parallelism to be produced. There is no
saving results into memory - you either save them explicitly or iterate
over them and the later prevents parallelism as you've noted.
David J.
>
into a
variable should leverage parallelism if the query is amenable to it.
David J.
On Wednesday, November 22, 2023, Efrain J. Berdecia
wrote:
>
> Thanks in advance for any suggestions or the green light to post this to
> the PG-developer group :-)
>
If you aren’t offering up a patch for these it isn’t developer material and
belongs right here.
David J.
others lives easier by giving more evidence
and self-contained example; but the assertions being made are complete.
David J.
On Thursday, November 23, 2023, Tom Lane wrote:
> "David G. Johnston" writes:
> > On Thursday, November 23, 2023, Tom Lane wrote:
> >> This question is unanswerable as given. You have not even defined
> >> what you mean by "fail" (error? wrong
d
normally be.
>
> I added "log_hostname = on" to postgresql.conf earlier in the day. When I
> commented that out, the errors stopped happening.
>
This makes sense.
David J.
On Sat, Nov 25, 2023 at 12:22 PM Davin Shearer
wrote:
>
> Is there a way to emit JSON results to file from within postgres?
>
Use psql to directly output query results to a file instead of using COPY
to output structured output in a format you don't want.
David J.
n that format you get the raw unescaped contents of
the column. As soon as you ask for a format your json is now embedded so it
is a value within another format and any structural aspects of the wrapper
present in the json text representation need to be escaped.
David J.
On Monday, November 27, 2023, Pavel Stehule wrote:
> Hi
>
> po 27. 11. 2023 v 14:27 odesílatel David G. Johnston <
> david.g.johns...@gmail.com> napsal:
>
>> On Monday, November 27, 2023, Dominique Devienne
>> wrote:
>>
>>> There's even a
for such a simple linear procedure becomes pointless since a
transaction will already commit or rollback appropriately depending on
whether the procedure provokes an exception.
David J.
On Fri, Dec 8, 2023 at 8:45 AM David Gauthier wrote:
>
> I'm trying to run a PG client side "\copy" command from a perl script. I
> tried using $dbh->do("\\copy ...") but it barffed when it saw the '\'...
> ERROR: syntax error at or near "
On Fri, Dec 8, 2023 at 9:01 AM Adrian Klaver
wrote:
> On 12/8/23 07:45, David Gauthier wrote:
> > Hi:
> >
> > I'm trying to run a PG client side "\copy" command from a perl script.
> > I tried using $dbh->do("\\copy ...") but it barffed
ocs/current/sql-createfunction.html (see notes)
David J.
that is known to produce the observed behaviors
on non-RDS PostgreSQL.
David J.
On Thu, Dec 14, 2023 at 5:46 PM wrote:
> On Thu, 14 Dec 2023 13:10:16 -0500 Ron Johnson wrote:
>
> >> I'm not sure if you kept the line, but you have ellipsed-out ( is that
> >> a word? )
>
> ellipse: curve
> ellipsis: ...
>
>
Though in contect "redacted" makes sense too.
David J.
The way to check if your email was received by the list is to search for it
in the archives.
https://www.postgresql.org/search/?m=1
Please stop sending testing emails to the entire hundreds of thousands or
millions of people on this list.
David J.
On Tuesday, January 2, 2024, Ray O'Do
ely in our PostgreSQL-based apps,
> and I've read a lot about them, but at times I feel I'm missing something.
>
>
Membership no longer does anything by itself. Both inherit and set
capabilities are now individually controlled permissions related to
membership. It is indeed possible, but not useful, to grant membership but
then disallow both set and inherit permissions.
David J.
On Tue, Jan 2, 2024 at 9:21 AM Dominique Devienne
wrote:
> On Tue, Jan 2, 2024 at 5:11 PM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Tue, Jan 2, 2024 at 8:25 AM Dominique Devienne
>> wrote:
>>
>>> pg_has_role() from
>>>
t. The effects
of such a statement will not be predictable.
"""
Yes, an error would be nice, but the effort put forth stops at
unpredictable, and saying just don't do it.
David J.
.
So there is overhead but also savings. The net effect is case specific.
David J.
maybe they report them to a
more appropriate NB oriented place?
David J.
lpgsql;*
>
>
>
> However, I get an error, and I can't compile the function.
>
> *Thank you very much for your help*
>
>
You did nested dollar quoting wrong; and I don’t see the word create where
you try to define the function inside the outer function.
You are probably better off just defining two functions independently
anyway, there is minimal benefit to having on function define another in
PostgreSQL, there are no closures.
David J.
culate it?
>
>
IIRC it’s the amount of RAM on your instance. You look it up in a table
usually. Or check the web console.
David J.
On Wednesday, January 17, 2024, Troels Arvin wrote:
>
> Is it possible to call pg_dump (or equivalent action) through a
> procedure/function?
>
Are you able to install an untrusted language handler into the database?
They are untrusted because they can basically get shell on the se
cs/current/catalog-pg-constraint.html
David J.
d on 14.9 to fix this issue.
>
>
>
Next weeks releases (14.11; 16.2) include the patch.
https://www.postgresql.org/message-id/flat/E1r81yU-007giA-5z%40gemulon.
postgresql.org
David J.
t ]::point[]
The main problem is the concept of writing "from($1)" in any query makes no
sense, you cannot parameterize a from clause directly like that. You have
to put the value somewhere an expression is directly allowed.
David J.
n the earliest 50 rows per user to answer this question.
David J.
On Mon, Feb 5, 2024 at 4:09 PM David Gauthier wrote:
>
> I want the result to be just 2 recs, one for each dog.
>
My present goto link for this question:
https://harshitjain.home.blog/2019/06/17/postgresql-how-to-delete-duplicate-rows-in-postgresql/
David J.
Regardless of the size of the actual data in a variable width column expect
that the size and computational overhead is going to make using that field
more costly than using a fixed width field. You don’t have a choice for
text, it is always variable width, but for numeric, if can use an integer
variant you will come out ahead versus numeric.
David J.
implement a
server-enforced policy, removing the ability for clients to do arbitrary
queries on the underlying tables. The checkout function can tag who got
the job and the completion function can validate the input arguments
supplied by the client belong to a job they checked out.
David J.
a type choices are unconventional and even arguably wrong for using
double for currency.
>
> *I am getting no output for using:*
>
> FROM
> system."IMETA_ZTRB_MP$F_ZTBR_TA_BW2" z
> WHERE
> z."Fiscal_Year" = 2024
>
Your table contains no matching rows. The use of left join basically
precludes any other explanation.
David J.
ache_mode
https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-PLAN-CACHE-MODE
David J.
On Tuesday, February 13, 2024, veem v wrote:
>
> float data types rather than numeric. This will give better performance.
>
Only use an inexact floating-point data type if you truly understand what
you are getting yourself into. Quickly getting the wrong answer isn’t
tolerable solutio
llow for the underlying
storage representation to be the same.
David J.
On Thu, Feb 15, 2024 at 4:31 PM Peter J. Holzer wrote:
> On 2024-02-14 22:55:01 -0700, David G. Johnston wrote:
> > On Tuesday, February 13, 2024, veem v wrote:
> >
> > float data types rather than numeric. This will give better
> performance.
> >
> >
>
uence name for the new one.
"reattach the identity" isn't a thing - the system is telling you it is
creating a new one and you will need to synchronize it to your data.
David J.
art of it. So you can modify the values to be what you need.
David J.
enclosed in single quotes.
The functional constructors for ranges allow you to specify a single string
literal containing both as the third argument.
David J.
; It wasn't obvious to me when I came to postgres from oracle.
>>
>> Well, that depends on INHERIT on the ROLE, and since v16 on the
> membership GRANT, probably.
>
Prior to v16 only the attribute mattered. Since v16 only the membership
option matters.
David J.
>
usage/behavior of a
sequence defined this way and forgo the enforced PK constraint.
David J.
is always valid - except in the OVERRIDING SYSTEM VALUE case - no?
>
False.
ALTER TABLE … ALTER id RESTART 1;
David J.
On Monday, February 19, 2024, David G. Johnston
wrote:
> On Monday, February 19, 2024, Darryl Green wrote:
>
>>
>> > It may be possible to still have it work by doing a speculative record
>> > in the index for the target table then go and check all of the other
&
and I thought
> that this could only happen if we ran the underlying query, not the Mat
> View?
>
>
>
>
>
> Have I got it wrong??
>
A materialized view is, physically, just like any other table. It is a
cache but only in the sense that tables cache real life data.
David J.
d to actually show your work. What pooler
you are using, how you are connecting.
Poolers don’t tend to play nice with clients that want to supply connection
settings upfront. Either tell the client to not do that or don’t use a
pooler - the server itself is quite content with connection settings.
David J.
nowledge as to the underlying query
that built the MV physical relation.
David J.
On Thursday, February 29, 2024, normandavis1990
wrote:
>
> What is the difference between High Availability and Replication?
>
The former is a goal, the later is a technique.
David J.
uating a time zone without both date
and time inputs is basically pointless.
> money is a fixed-point decimal value, the number of decimal places is
locale determined. I’m not aware of any particular problems with that
You forget about the currency symbol dynamic. Like with time zones the
local session provides the context, not the stored data.
David J.
On Thu, Mar 14, 2024, 11:08 Thiemo Kellner
wrote:
> Thanks for the enlightenment. A pity. I suppose, there is no working
> around this?
Write a script to do the query in a loop on all databases - that catalog is
global.
David J.
no
relationship to how some other unrelated view performs. That the views
have the same name is just bad naming/design for this very reason; it harms
understanding.
David J.
gnose. Given the time difference
if it isn't fundamentally a different view then I'd be inclined to suspect
locking issues as a probable factor.
David J.
>
you don't want to happen and disabled those things.
David J.
>
ally
if we are altering catalog metadata to define the columns to be not null,
as opposed to say the case when a check constraint has a "col is not null"
condition that could never pass even though the column itself is null-able.
David J.
t we might want to request to investigate the performance problems?
>
PostgreSQL roles are defined here:
https://www.postgresql.org/docs/current/predefined-roles.html
David J.
I’d suggest searching the mailing list archives for prior discussions.
David J.
On Fri, Mar 29, 2024 at 2:16 PM David Gauthier
wrote:
> I tried encapsulating the DB name in double quotes (no good)
>
This is what the documentation says you are supposed to do for non-simple
identifiers so you need to show your work to understand where you went
wrong.
David J.
On Fri, Mar 29, 2024 at 2:20 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Fri, Mar 29, 2024 at 2:16 PM David Gauthier
> wrote:
>
>> I tried encapsulating the DB name in double quotes (no good)
>>
>
> This is what the documentation says you
first. So I share it here. For more details please check out the README
file.
https://github.com/polobo/RoleGraphForPostgreSQL
Feedback is much appreciated.
Thanks!
David J.
changed
current_user to the definer of that function and won’t change back until
the function returns. Which it hasn’t when inner function is invoked.
David J.
On Sun, Apr 7, 2024 at 9:10 AM Ayush Vatsa wrote:
>
> but what about the view which contains inbuilt operators or inbuilt
> functions with whose privileges those will be executed. Eg.
>
>From the create view documentation:
Functions called in the view are treated the same as if they had been
cal
tion with special syntax. So I expect that
sentence to apply.
If you want to confirm what the documentation says create a custom
operator/function that alex is not permitted to execute and have them query
a view defined by postgres that uses that function.
David J.
greater-than really cares.
David J.
ee https://www.postgresql.org/docs/current/ddl-priv.html
Especially the part regarding default privileges. The PUBLIC pseudo-role
is granted execute on functions by default. You are probably trying to
revoke a privilege from alex that was never granted to alex directly.
David J.
table.html
>
Much of the details regarding the things you can alter onto a table are
defined in create table reference for the same thing.
David J.
st ensure
you are doing them on a staging table that is defined as either being
temporary or unlogged. WAL production probably isn't causing the crash but
can definitely be an issue and is pointless to incur during most
transformation processing.
David J.
8 years.
Whether related or not to this error you really should be doing all those
updates on a temporary or unlogged staging table and getting WAL generation
out of the equation.
David J.
ant app_user
permission to execute it.
David J.
On Sunday, April 21, 2024, yudhi s wrote:
> On Sun, Apr 21, 2024 at 1:55 PM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Sunday, April 21, 2024, yudhi s wrote:
>>
>>> so that it will be able to assign the privilege, so we will be able to
On Sun, Apr 21, 2024 at 11:10 AM yudhi s
wrote:
>
> On Sun, Apr 21, 2024 at 7:55 PM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Sunday, April 21, 2024, yudhi s wrote:
>>
>>> On Sun, Apr 21, 2024 at 1:55 PM David G. Johnston
On Sun, Apr 21, 2024 at 11:20 AM yudhi s
wrote:
>
> On Sun, Apr 21, 2024 at 8:13 PM Tom Lane wrote:
>
>> "David G. Johnston" writes:
>> > On Sunday, April 21, 2024, yudhi s wrote:
>> >> Are you saying something like below, in which we first crea
ust return normal results and the client takes them
are merges them.
David J.
r but
> that's not the case with postgresql 16 we keep getting the error message
> 'permission denied to grant role "pg_signal_backend".
>
Probably you haven’t granted pg_signal_backend to whichever role you are
executing the above grant command with. One cannot grant wha
n option;
GRANT ROLE
postgres 2=> grant pg_signal_backend to otherrole;
GRANT ROLE
David J.
e data is already roughly in PK order.
>
If things are bad enough to require a vacuum full that doesn't seem like a
good assumption. Any insert-only table or one with a reduced fill-factor
maybe.
David J
html
And none seem to involve the generated expression column. So the answer is
no.
David J.
sical file. It's a tin-can-and-string solution to
interprocess communication.
David J.
the port number 5432 from networking to construct the
unique name out of convenience.
David J.
there a way I can retrieve this master password?
>
Nope. You need to login using a method that doesn’t require a password
then change the password. See pg_hba.conf for authentication options.
Usually connecting via local and peer authentication is the default option.
David J.
On Sat, Apr 27, 2024 at 11:07 AM Sasmit Utkarsh
wrote:
>
> But i have some clarifications if we can use it to execute PLSQL code
> blocks rather than standalone SQL queries.
>
https://www.postgresql.org/docs/current/sql-do.html
David J.
there should be functions or a query to perform the needed lookup.
https://www.postgresql.org/docs/current/datatype-oid.html
David J.
bject_metadata but that I suspect would be
the extent to which we'd do something along the lines of your request.
David J.
but verify - i.e., use something like pg_audit.
David J.
On Fri, May 3, 2024 at 2:08 PM Adrian Klaver
wrote:
> On 5/3/24 14:06, Magnus Hagander wrote:
> >
> >
> > On Fri, May 3, 2024 at 10:58 PM David Gauthier > <mailto:dfgpostg...@gmail.com>> wrote:
> >
> > psql (15.3, server 14.5) on linux
> >
reality to the rest of
the system, by virtue of marking the transaction live. If the commit never
happens, either because of error, rollback, or session end, the transaction
ends up being left unalive and eventually is cleaned up.
You need to ensure a “begin” happens before Step 1 and a “commit” after
Step 3.
David J.
On Friday, May 3, 2024, David G. Johnston
wrote:
> On Friday, May 3, 2024, Siddharth Jain wrote:
>
>>
>>
>> On Fri, May 3, 2024 at 8:00 PM Siddharth Jain wrote:
>>
>>>
>>>
>>> The way I understand this is that if there is a failure in
oice more power to them, making \d more accessible
for them is a win and snake case people won’t notice or care.
David J.
xperience to see things differently.
I also get not wanting to change behavior at this point though I’d welcome
a modifier like “*” (like the ~* operator) to enable case-insensitive
matching.
David J.
”infrastructure errors”, or otherwise. The only classification
is severity.
It does include an SQL Error Code that you could, in post-processing, act
on.
David J.
left join is evaluated and so other tables in the comma join cannot
appear in the on clause of the left join. Placing everything inside a
single from slot and moving the conditions to the where clause removes
changes the precedence aspect so that the cross join does indeed evaluate
prior to the left join.
I’m content with not pointing out this possible gotcha in the documentation.
David J.
mpany table twice
into the FROM clause of the query was a good idea maybe we can help you
unlearn that bad belief. Otherwise feel free to just take the answer
you've been given.
David J.
amusing, it is breaking my backup script after update
> from 11 to 15 (and change from pg_{start,stop}_backup to
> pg_backup_{start_stop})
>
Yep, nowadays you must keep the transaction where you issued backup start
open until you issue backup end. Using -c isn't going to cut it.
David J.
>
1701 - 1800 of 2422 matches
Mail list logo
