On Monday, July 14, 2025, Laurenz Albe wrote:
> On Mon, 2025-07-14 at 12:19 -0700, Rich Shepard wrote:
> > On Mon, 14 Jul 2025, David G. Johnston wrote:
> >
> > > The error indicates your script file is at least 127 lines long and you
> > > are showing like
exists (
select from enforcement as e
where e.company_nbr = c.company_nbr
)
group by c.industry;
David J.
w.postgresql.org/docs/current/datatype-json.html
>
>
I've taken to heart the main takeaway from that page:
"In general, most applications should prefer to store JSON data as jsonb,
unless there are quite specialized needs, such as legacy assumptions about
ordering of object keys."
David J.
d is the error was a simple typo, choosing the
wrong file to execute. Not reading the error message was the real issue,
not a failure to understand how psql scripting works.
David J.
uot;
> LINE 1: company_nbr | company_name
> ^
> and I'm not seeing the error. What am I missing?
>
The error indicates your script file is at least 127 lines long and you are
showing like 9...also do you usually name your script files with a .txt
extension?
David J.
On Mon, Jul 14, 2025 at 12:02 PM Benjamin Wang
wrote:
> I am not sure whether PostgreSQL depends on system call `fsyncdata` to
> sync data to disk.
>
https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-SYNC-METHOD
David J.
supported configuration.
David J.
suggests
this.
David J.
e, hopefully you can just change
all passwords to use scram and move on.
David J.
ol file appears to be corrupt
David J.
On Wednesday, July 2, 2025, Gaurav Aradhya wrote:
>
> Can you please let me know when Postgresql 17.x shall be supported for
> Windows Server 2025? Greatly appreciated your feedback.
>
Impossible to guess when someone may choose to set up a build farm member
running that OS.
David J.
On Wednesday, July 2, 2025, Durumdara wrote:
>
>
> Is there any way to avoid this? To use a "simple untyped record" in an
> array without "dependencies"?
>
Use jsonb
David J.
form you’ve shown here is part of that.
Not sure we’d turn it down but someone will need to step up and scratch
their own itch in all likelihood (the request for similar functionality in
the result comes up too).
David J.
ng away data you may be
experimenting with during a point-in-time recovery.
David J.
gap for arrays. Scalars you can just cast and composites have
these functions. But no simple/direct way to go from json array to sql
array is presently implemented.
Though since 17 json_query can apparently do it.
select pg_typeof( json_query('[1,2,3]'::jsonb, '$' returning integer[]) )
-> integer[]
David J.
act how pgAdmin behaves but it is
how you tell the server where you are. Strongly advise using the full
formal name and not an abbreviation or offset.
David J.
to be the value for max_connections. It is not clear to me
> what "... connection limit of 30% of the max_connections setting" is
> referring to?
>
See "alter database ... set"
David J.
hat PG10 was obsolete when 24.04 came out this isn’t a shock
to me.
David J.
On Tue, Jun 10, 2025 at 1:01 PM Jim Cunning wrote:
> I obviously no longer have a running version 10 server,
>
You will need to correct this lack if you want to make use of version 10
data files.
David J.
e that ran. Analyze will try to
estimate the live and dead rows, but since analyze only samples some
blocks, it may come up with something that's not too accurate if the
blocks it happened to sample don't contain similar percentages of dead
rows than the entire table.
See [1].
Dav
On Monday, May 19, 2025, Dias Thomas wrote:
> Hello all,
> Could i get a help, postgres 1 billion records indexed table, search
> speed in a normal machine, no parallel processing ... for a knowledge ??
>
https://wiki.postgresql.org/wiki/Slow_Query_Questions
David J.
.918.41"
part that you're seeing in the EXPLAIN output.
You could try adding an index that suits all your equality WHERE
clause filters, or some subset of them and put the date column as the
final indexed column and see what happens.
David
ndex is corrupted".
> I have no idea what is going on or how to deal with it and will be
> grateful for advice
>
>
>
You will need to rebuild the index.
If you've recently performed an OS upgrade you may also wish to update
most/all indexes to fix possible collations issues.
David J.
normal? How can I simply resolve the problem?
Calculate the dat column in a subquery in the outer-level query.
Something like:
FROM (select *,to_char(dat, 'MM') as strdat from table1) table1
JOIN table2 USING (num_poste)
then use strdat in all the places you're currently using to_char(dat, 'MM')
David
On Tuesday, April 29, 2025, Tim Starling wrote:
>
>
> This is a public interface and there may be callers in code that I don't
> have access to.
>
You might help your cause by sharing examples of how client code uses your
driver to perform upsert that runs into this limitation.
David J.
to force
everyone to rewrite their RETURNING statements when upgrading to v18.
The moral of that story is, UPDATE isn't exactly consistent already
about when it requires column qualifications. Maybe it's weird to
insist that users qualify columns with their ON CONFLICT UPDATE SET
when RETURNING is happy to assume you must have meant NEW.
David
ocabulary word "jovial" if that word is
not already in the dictionary, or if it is already in the dictionary, it
increments the counter. The "count+1" expression could also be written as
"vocabulary.count". PostgreSQL requires the second form, but SQLite accepts
either.”
https://sqlite.org/lang_upsert.html
David J.
the broader
world. I am curious as to whether we are in strict adherence to the SQL
Standard on this point though. Makes deviation a bit tougher to justify.
It does seem that project policies would prevent back-patching such a
change.
David J.
t's possible that you can use an event trigger [1]
that triggers on CREATE TABLE to get what you want here. If the docs
don't give you enough information, then maybe [2] will have something
you can work with. I've not used them much, so I don't have any
recipes for you.
David
to output the query against that table?
David J.
rmatively for the tuples and error cases
and have a final else should the status be something unexpected.
David J.
l PQclear() on every iteration of the loop?
>
Would make processing more than a single row impossible if you throw away
the result after processing one row.
David J.
On Fri, 25 Apr 2025 at 03:06, Laurenz Albe wrote:
>
> On Fri, 2025-04-25 at 01:41 +1200, David Rowley wrote:
> > The 79.3 seconds is the total time spent doing reads for all parallel
> > workers. 52.6 seconds is the wall clock time elapsed to execute the
> > query.
>
&g
; I/O Timings: read=79368.246 write=11.486
>
> So, the total execution time is 52655 ms ok
> and the total time for i/o is...79368 ms
>
> how ???
The 79.3 seconds is the total time spent doing reads for all parallel
workers. 52.6 seconds is the wall clock time elapsed to execute the
query.
David
On Wednesday, April 23, 2025, Igor Korot wrote:
>
> The question is more about the default value...
>
0 or 1, determined at server compilation time. You quoted the
documentation that says this…
David J.
If they try and it errors, it doesn’t
support it.
David J.
same can be sad about
> https://www.postgresql.org/docs/current/runtime-config-
> query.html#GUC-RANDOM-PAGE-COST
Costs can’t be negative and no reasonable positive value is going to exceed
the data type limit, which is communicated via the data type specification.
David J.
igm, you can’t
remove columns from existence on a per-row basis.
David J.
You can do what you want if you directly insert into the individual
partitions, using the partitioned table as a read-only interface.
It looks like this:
https://dbfiddle.uk/Xc3cUHSO
David J.
I don't find that the information is hard to understand so no, I'm not
going to spend my time summarizing it in an email.
David J.
On Sat, Apr 19, 2025 at 6:06 AM sivapostg...@yahoo.com <
sivapostg...@yahoo.com> wrote:
> By any chance, if I get that statement, what should I do?
>
Read it.
What are the Steps (or documentation) to correct this issue?
>
>
Impossible to say until the statement is read.
David J.
of your
databases UUID names and ensure that non-superusers must be told the
databases they are allowed to connect to.
But feel free to work out a design and add it to the ToDo list for the v4
protocol. The use case seems reasonable and doable (on the basis of the
replication protocol works).
https://wiki.postgresql.org/wiki/Todo#Wire_Protocol_Changes_.2F_v4_Protocol
David J.
n or experience that they are
"postgres", "template1" and "template0".
Pick whichever of "system" or "convention" or some other word makes you
happy to categorize those three databases. Though I suggest "the initdb"
databases since neither of the other terms is used anywhere in the
documentation.
David J.
On Tue, Apr 15, 2025 at 11:20 AM Adrian Klaver
wrote:
> On 4/15/25 09:48, David G. Johnston wrote:
> > On Tue, Apr 15, 2025 at 9:31 AM Adrian Klaver > <mailto:adrian.kla...@aklaver.com>> wrote:
> >
>
> >
> > Agreed.
> >
> > The
On Tue, Apr 15, 2025 at 9:31 AM Adrian Klaver
wrote:
> On 4/15/25 09:21, Igor Korot wrote:
> >
> >
>
> > Hi, David,
> >
> > On Tue, Apr 15, 2025 at 9:56 AM David G. Johnston
> > mailto:david.g.johns...@gmail.com>> wrote:
> >
> >
On Tuesday, April 15, 2025, Igor Korot wrote:
> Hi, ALL,
> Is there a field in the pg_databases table which indicates that particular
> DB is a system one?
>
What is a system database?
David J.
version that just confused
the issue. Why point out exclude blobs if you know they are using bytea?
David J.
On Friday, April 11, 2025, sivapostg...@yahoo.com
wrote:
>
>
> bytea field also included in the backup.
>
Bytea typed columns are completely separate things than large objects. You
cannot exclude individual columns using pg_dump.
David J.
On Friday, April 11, 2025, Igor Korot wrote:
> Hi, David,
>
> On Fri, Apr 11, 2025 at 9:04 PM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Fri, Apr 11, 2025 at 6:49 PM Igor Korot wrote:
>>
>>> Hi, ALL,
>>> On the http
P-Gist:
fillfactor
GIN:
fastupdate
gin_pending_list_limit
BRIN:
pages_per_range
autosummarize
David J.
volatility of the used functions and compare
them to the volatility of the UDF.
David J.
unction volatility match the most volatile function you internally
call - constant input arguments don’t change this.
There is no reason to perform number formatting immutably - function call
results involving table data are not memoized.
David J.
olution setup this might impose a
non-negligible
+performance penalty.
This parameter can only be set in the
postgresql.conf
file or on the server command line.
Both the connection logging routine and log_line_prefix %h / %r simply
report the "identifier of the host making the connection".
David J.
y because \df+ does show this information as well, so at least one
doesn't have to go write the catalog query themself.
David J.
IMMUTABLE.
>
So, the punishment for lying about the volatility of one's function is to
prohibit it from being inlined even in a case where had you been truthful
about the volatility it would have been inlined.
David J.
>>>>>>
> PSQL should not have asked for the password.
> Thanks
> Danny
>
Suggests there are environment variables coming into play making
non-default choices for you. Namely, making you not use local as the
connection method.
David J.
On Wed, Apr 9, 2025 at 9:20 AM Abraham, Danny wrote:
> Fail
>
Failures include messages indicating why. You should always share such
messages. Showing the command that produced the failure is also advised.
David J.
els if doing this check on
large bloated tables is what your existing choices have led to.
David J.
On Thu, Mar 20, 2025 at 2:56 PM Tom Lane wrote:
> "David G. Johnston" writes:
> > On Thu, Mar 20, 2025 at 11:54 AM Tom Lane wrote:
> >> I think it's a mistake to suppose that pg_type_d.h is the only
> >> place where there's a risk of confusi
On Sunday, March 23, 2025, David G. Johnston
wrote:
>
> Maybe IOS helps though I do wonder whether a sequential scan skips over
> known all-dead pages making that relative benefit go away.
>
Well, no, since it tracks known visible, not known non-visible, though for
something like a
On Sat, 29 Mar 2025 at 06:00, Evgeny Morozov
wrote:
>
> On 23/03/2025 2:35 pm, David Rowley wrote:
> >> alter table entity_2 add column new_column text;
> > Is this just an example command? You can't add a column to a
> > partition directly.
>
> Yes, it was
of assuming an even
distribution, you use that minimum value to tell you what percentage
of the index must be read before a match is found. The stored maximum
position value would do the same job for backward index scans.
David
y is
positional information of where certain values are within indexes
according to an ordered scan of the index. I don't quite know how we'd
represent that exactly, but if we knew that a row matching col_a >
4996 wasn't until somewhere near the end of idx_col_a_btree index,
then we'd likely not want to use that index for this query.
David
pose.
There is a way to get a different interpretation for IST to be recognized
but I'd have to find it or wait for others to chime in.
David J.
from the manual
because people are bad at predicting the future. Just document what is and
don't string people along. We never go back to old releases and say "This
was fixed in v17."
David J.
rnative approach for that use
case could be suggested.
David J.
g in the relevant spot in [1].
David
[1]
https://postgr.es/m/CAApHDvogvzANoTOCyXUWgEuPFx1nT6S63aAN0bDRSJ=tagb...@mail.gmail.com
alues you are actually going to be filtering on. So low cardinality
booleans can be highly selective in usage if you are looking for the rare
false in a sea of trues but low selectivity if looking through those trues.
David J.
On Fri, Mar 28, 2025 at 10:02 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> Selectivity is evaluating cardinality with an eye on the frequency of
> the values you are actually going to be filtering on. So low cardinality
> booleans can be highly selective in u
On Sunday, March 23, 2025, Igor Korot wrote:
>
> 2. Is there a way to do CREATE TRIGGER IF NOT EXIST for the earlier
> version?
>
No. You have to drop the trigger if it does exist and then create the new
one.
David J.
On Sunday, March 23, 2025, David G. Johnston
wrote:
> On Sunday, March 23, 2025, Igor Korot wrote:
>
>>
>> 2. Is there a way to do CREATE TRIGGER IF NOT EXIST for the earlier
>> version?
>>
>
> No. You have to drop the trigger if it does exist and then cr
On Sunday, March 23, 2025, Igor Korot wrote:
>
> CREATE TRIGGER playersinleague_insert AFTER INSERT ON playersinleague
> WHEN new.current_rank IS NULL
>
>
When the syntax shows parentheses you are required to write them.
[ WHEN ( *condition* ) ]
David J.
without exists #1 returns either an empty set
or an , never NULL.
Always returning a Boolean seems like a better API choice though; but
standardizing on that final transform doesn’t change the base comparison.
David J.
'server_version_num')::int > =14 as v14
>
IOW, you can’t use >13 because that will match v13.1 which is 130001 in
integer format.
David J.
not a true group role I suspect inherit/noinherit doesn’t
apply. (You also cannot SET to it, nor admin it - not tested.) Losing the
execute privilege on every built-in function would be way too annoying.
David J.
LAIN ANALYZE.
It is possible to determine the number of partitions which were
removed during this phase by observing the “Subplans Removed” property
in the EXPLAIN output."
Perhaps something like. "It's important to note that any partitions
removed by the partition pruning done
On Saturday, March 22, 2025, Igor Korot wrote:
>
>
>> Is it actually running in psql?
>>
>
> Yes, i run "psql - d draft -a -f
>
Then read the psql docs. Your version has \if and you’ll find server
version listed as the available client variables.
David J.
t code" would seem mostly interested in these
OIDs and not stuff like the attribute numbers of the columns in pg_type. I
get a distinct feel of one file serving multiple use cases.
> As for CASHOID and LSNOID, surely those have been deprecated long
> enough that we could just remove them?
>
>
I'd probably just leave them.
David J.
e invested in the outcome you have more motivation than probably anyone
else to dive into it and make concrete suggestions for change.
All that said, a comment at the top of what is probably the most important
section of the header seems warranted. Even if it is just mostly
formality. Mentioning the constant-ness of the integers should be part of
that.
David J.
m PUBLIC. You have to revoke a granted privilege.
David J.
ssumption is that at moderate to high reuse volumes it is quite
probable that a generic plan will win or at least be acceptable in 999 in
1000 or more executions, not 4 in 5.
David J.
'BOX'::text) AND (competitionid = 'U-1998'::text) AND (eventid =
>>> 'U-523596'::text))
>>> Heap Fetches: 1
>>> Planning Time: 0.084 ms
>>> Execution Time: 0.043 ms
>>>
>>
>>
>> To return one row takes 43ms is not optimal
>>
>
You are off by a factor of 1000 in your claimed performance. It’s 0.043ms
David J.
wn IDs as part of the test data specification.
David J.
null);
>
> Any suggestions would be appreciated.
>
>
Using "equals" or "not equals" when one of the inputs can be null is not
usually what you want to do. The comparison evaluates to NULL which the
constraint allows.
David J.
rent/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-STATEMENT
See: client_min_messages
David J.
EN CREATE UNIQUE INDEX
> \"abcatc_x\" ON \"abcatcol\"(\"abc_tnam\" ASC, \"abc_ownr\" ASC,
> \"abc_cnam\" ASC); END IF; END;" );
> [/code]
>
> What am I missing?
>
“ ERROR: unterminated dollar-quoted string at or near‘
The terminator for your dollar-quoted string.
David J.
logical images of the database so applying WAL
on top of pg_restore is technically invalid - but it does effective convey
the idea. It’s like saying pg_dump and pg_basebackup are similar. Sure,
in some ways that is true - but the logical vs. physical distinction cannot
be ignored fully.
David J.
t is only possible of you’ve
backed up the data files at some point in the past and use that backup.
David J.
On Saturday, March 1, 2025, Krishnakant Mane wrote:
>
> Can you tell me how exactly should the syntax be?
>
https://www.postgresql.org/docs/current/ddl-schemas.html
> Is my function definition wrong?
>
It isn’t a function definition, it is a function call.
David J.
tputs from the above three commands in your response.
>
> See this issue for why this is different now.
https://github.com/sraoss/pg_ivm/pull/116
Then deal with the fact it is no longer installed to pg_control but instead
pgivm. With usual schema qualifications or search_path techniques.
David J.
e
feature isn't driving its development the odds of it getting worked on is
fairly low.
David J.
s you have no way to know what the
caller has specified as the return data type in order to write the inner
generic function call correctly. Maybe you can convert the record result
to jsonb and return that?
David J.
source; or use
something like pgbouncer.
David J.
lumn, for all existing rows, when it was executed. While the value
of the substitute is equal to the non-volatile default specified for the
column it is an independent thing. Subsequently dropping or changing the
default does not impact this substitute value. There is no way to impact
the substitute value via SQL that I know of.
David J.
nything in this case.
> So I don't see how these recommendations are relevant to this particular
> case. --DD
>
>From “drop owned”:
Any privileges granted to the given roles on objects in the current
database or on shared objects (databases, tablespaces, configuration
parameters) will also be revoked.
So, the command does more than the name suggests.
David J.
le. Does the combination of reassign
and drop owned not fulfill the requirements?
https://www.postgresql.org/docs/current/role-removal.html
Also, you had to know the role you wanted to drop so you already figured
out the grantor.
David J.
n. Why isn't it?
>
This has nothing to do with power/permissions. It is about not specifying
“granted by” in your SQL command and thus failing to fully and correctly
specify the single permission you want to revoke.
David J.
On Thu, Feb 20, 2025 at 9:05 AM Tom Lane wrote:
> "David G. Johnston" writes:
> > On Thursday, February 20, 2025, Dominique Devienne
> > wrote:
> >> Hi. Today I was surprised that REVOKE ALL ON DATABASE FROM ROLE silently
> >> did nothing, even with
On Thursday, February 20, 2025, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Thursday, February 20, 2025, Dominique Devienne
> wrote:
>
>> Hi. Today I was surprised that REVOKE ALL ON DATABASE FROM ROLE silently
>> did nothing, even with CASCADE, when I
gt;
> It doesn't like that reference to "d.dvm_id) in that subquery.
You could use LATERAL before the subquery in the FROM clause, or you
could adjust the subquery by removing the "where dvm_id=d.dvm_id"
replacing it with GROUP BY dvm_id and adding that column to the SELECT
list and include that in the join condition between the tables.
David
1 - 100 of 1815 matches
Mail list logo
