may face latency issues and lower environment this issue cannot
> be reproduced,( this is happening out of Million one case)
>
> This looks puzzle to us, just in case anyone experianced pls share your
> experience.
>
> Regards,
> Ravi
>
> On Thu, 7 Nov, 2024, 3:41 am David
On Mon, 25 Nov 2024, 06:08 Shaun Robinson,
wrote:
> Hi,
>
> I'm currently testing an application with Postgres 17.2 and am getting an
> error when creating a materialized view which works in version 16 and
> below. The sql works fine running as a query, but adding the
> create materialized view b
i dont get why you think all memroy will be used.
When you say
shared_buffers = 16GB
effective_cache_size = 48GB
...then this is using only 16GB for shared buffers.
The effective _cache_size doesn't cause any memory to.be allocated. It's
just a hint to optomizer
On Wed, 20 Nov 2024, 11:16
nstants [2] require single quotes or the dollar quoting
format and 5min isn't a valid number.
David
[1] https://www.postgresql.org/docs/17/sql-set.html
[2]
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS
Instead of nickname you probably want tontet where password=`Arbol' .. or
am.i.missong something ?.
On Fri, 22 Nov 2024, 20:13 David G. Johnston,
wrote:
> On Fri, Nov 22, 2024 at 1:07 PM Arbol One wrote:
>
>> The below sql statement produces the right output
>> SELECT
;. We maybe could do a bit more work to initialise those
more lazily as we do for INSERT statements, but I'd be surprised if it
was a problem for 64 partitions, especially so for an update statement
that might be touching 3 months of data. Nothing about these existing
in the "Update on" portion of the EXPLAIN output means that that
partition will be scanned by the UPDATE statement, rest assured.
David
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
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 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
g in the relevant spot in [1].
David
[1]
https://postgr.es/m/CAApHDvogvzANoTOCyXUWgEuPFx1nT6S63aAN0bDRSJ=tagb...@mail.gmail.com
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
; 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
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
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 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
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
., a capable viewer would display whatever
00b0 is on-screen, or a placeholder if it is a non-printable character).
INSERT and COPY are two totally different animals:
INSERT INTO tbl (t) VALUES (trim(' jdjd ')); -- stores jdjd, but
putting trim(' jdjd ') in a csv file and you would store "trim('
jdjd')"
David J.
atabase and role names could
realistically and easily be done UUID-style so knowing the labels doesn't
really tell anything except a vague impression of host size.
Assuming clients don't want to see their log files...
David J.
,CURRENT_TIMESTAMP at time zone 'UTC' as ct4
>
This -
standard conforming and doesn't require assumptions about the calling
environment
,NOW() at time zone 'utc' as ct5
>
non-standard but frequently used; no semantic different compared to the
previous entry
David J.
lear them.
[1] https://www.postgresql.org/docs/10/static/storage-vm.html
David J.
On Tue, Feb 27, 2018 at 2:58 PM, Tom Lane wrote:
> "David G. Johnston" writes:
>
> > Also, I recall discussion that select statements will touch the
> visibility
> > map (hence causing write I/O even in a read-only query) but [1] indicates
> > that onl
gt; legitimate to do:
>
Add a query to your test suite that queries the catalogs and fails if this
policy is violated. There is nothing in a running PostgreSQL server
instance that is going to enforce this for you.
David J.
lready parsed query
takes less than 15 seconds and so doesn't appear due to your filter.
David J.
On Thursday, March 1, 2018, Vikas Sharma wrote:
> Thanks David,
>
> But why are there so many parse statement occurances for one query? Does
> postgres parse the statement everytime before execution or parse the query
> only first time it is loaded in memory and reuse the same pa
1 - but we've hidden the knowledge of c# behind the surrogate
key and now we can insert garbage into employee-department.
David J.
the specific choice to nouns doesn't make sense.
David J.
e strongest case, for me, when a surrogate key is highly desirable is
when there is no truly natural key and the best key for the model is
potentially alterable. Specific, the "name" of something. If I add myself
to a database and make name unique, so David Johnston, then someone else
comes alon
tions mutually exclusive.
David J.
_models_are_wrong
Unfortunately identifying a natural primary key doesn't guarantee that
one's model is mature, unblemished, and complete - the model writer may
just not know what they don't know. But they may know enough, or the
application is constrained enough, for it to be useful anyway.
David J.
upgrading to
a new minor release.
Major releases are a different matter though usually the upgrade happens
just fine but if you aren't careful to also update your applications they
could start working if you are using features that have changed
incompatibly, though we do try to minimize that.
David J.
the system think your array-looking string is actually just a
scalar that happens to have a leading [ and a trailing ]
David J.
On Fri, Mar 2, 2018 at 11:48 AM, Alexander Farber <
alexander.far...@gmail.com> wrote:
> Oops, I've got strings there too - when swapping instead of playing tiles:
>
>
You should probably add:
jsonb_array_length(tiles) > 0
as a check constraint on column
David J.
On Fri, Mar 2, 2018 at 11:58 AM, Alexander Farber <
alexander.far...@gmail.com> wrote:
> I see, thank you for your comments, David and Adrian.
>
> In the "tiles" column actually save either the JSON array of tiles - when
> the user plays them
>
> Or a string (w
t;
>
Use a service file.
psql "service=mydb" -c "SELECT 1;"
https://www.postgresql.org/docs/10/static/libpq-pgservice.html
I also, for scripts, simply define a function at the top of the script
psql_* and invoke that function instead of psql directly.
I use .pgpass for passwords
David J.
On Monday, March 5, 2018, Łukasz Jarych wrote:
> Anyone pleasE?
>
Don't know but you might get better results by emailing the support list
for the pgAdmin application. This list is for general questions about the
server.
David J.
On Tue, Mar 6, 2018 at 3:15 AM, Laurenz Albe
wrote:
> David G. Johnston wrote:
> > On Monday, March 5, 2018, Łukasz Jarych wrote:
> > > Anyone pleasE?
> >
> > Don't know but you might get better results by emailing the support list
> for the pgAdmin
On Tue, Mar 6, 2018 at 1:26 PM, David Gauthier
wrote:
> Hi:
>
> I'd like to grant select, insert, update, delete to a table for a specific
> set of uids (linux). All others get select only. Can the DB authenticate
> the current linux user and grant access based on th
ntication fails, subsequent records are not considered. If no record
matches, access is denied.
"""
David J.
On Wed, Mar 7, 2018 at 8:14 AM, Bjørn T Johansen wrote:
> On Wed, 7 Mar 2018 07:14:55 -0700
> "David G. Johnston" wrote:
>
> > On Wed, Mar 7, 2018 at 6:13 AM, Bjørn T Johansen wrote:
> >
> > > Hi.
> > >
> > > Is it possible to use one a
On Wed, Mar 7, 2018 at 10:21 AM, Martin Mueller <
martinmuel...@northwestern.edu> wrote:
> Given two values defined as integers, how do I divide one by the other and
> get an answer with two decimals, e.g 3 /4 = 0.75.
>
Case one of them to numeric.
select 3/4::numeric
David J.
are welcome.
The description "division (integer division truncates the result)" seems
reasonably clear - if you don't want the result truncated, and have two
integers, you have to make one of the inputs a non-integer. Of the various
options I tend to choose numeric though others are possible.
David J.
the "Data Type Formatting Functions"
chapter and the "to_char" function it describes.
David J.
ilar to Select than it is to Create - the object being
inserted into must already exist
David J.
ly stick with message text
parsing. If you have locale concerns you could just check for one of the
11 different translations that are presently provided. Or combine them,
checking for the error code value first (which probably would be rare
enough in production code to be usable) then fall back to the description.
David J.
ommand.
> > It is possible?
>
> I supose you mean execute the command contained in each row. Anyway,
> it is easy with a procedure, look for example at
>
> https://www.postgresql.org/docs/10/static/plpgsql-statements.html#PLPGSQL-
> STATEMENTS-EXECUTING-DYN
>
>
See also v10 psql \gexec
David J.
On Thu, Mar 8, 2018 at 2:30 PM, chris wrote:
> Given that the syntax looks correct for the url, how would we go about
> debugging that it's not seeing the comma?
First thing I'd do is ensure the version of the driver I'm using supports
the feature I'm trying to use.
David J.
u need it to do something different you
could teach it the desired behavior and add some relevant command line
switches to enable said behavior.
David J.
way to put them both
> on the same lin
>
https://www.postgresql.org/docs/10/static/libpq-pgpass.html
So unless you can make a wildcard work you are stuck with two separate
lines.
David J.
IOW, why do you need a "standby" that isn't a drop-in replacement for a
primary - i.e., can have a filter on what replicated data it accepts?
David J.
; inconsistencies in new schemas created by the library.
>
Then don't have a "create new schema" script (aside from the very first
one) run your migrations even when creating new installations.
David J.
hat A. TRIGGERS are meant for TABLES, not views*
>
> *and *
>
> *B. You CANNOT insert into a VIEW.*
>
>
Your knowledge is this area is out-of-date...you should read the page you
linked to again.
I'll admit "table_name" probably could be labelled "relation_name"...though
that is then too broad.
David J.
iew-pg-locks.html
and the pg_stat_activity view:
https://www.postgresql.org/docs/10/static/monitoring-stats.html#MONITORING-STATS-VIEWS-TABLE
The system doesn't really understand your model ID/PK field - the system
views and catalogs using system identifiers.
David J.
aim tasks by locking them,
setting a claimed flag of some sort, the releasing the lock (including
worker identity if desired) - repeating the general procedure once
completed.
My volume is such that the bloat the extra update causes is not meaningful
and is easily handled by (auto-)vacuum.
David J.
t list or as separate from/join items, and
then join the already aggregated queries together on their common group by
column.
The presence of DISTINCT here (and, IMO, generally), even if it worked,
would be an indicator that something is not quite right.
David J.
x27;) FROM tiles WHERE ta.mid = moves.mid) AS
mid_tiles,
(SELECT string_agg(words, '; ') FROM words WHERE words.mid = moves.mid) AS
mid_words
FROM moves
There are other ways to write that that could perform better but the idea
holds.
David J.
rds
> FROM cte1 JOIN cte2 using (mid) ORDER BY mid ASC;
>
>
Yes. It does end up presuming that the sets moves.mid and scores.mid
are identical but that is probably a safe assumption. Repetition of m.gid
= 1 is worth avoiding in theory though depending on how its done the
solution can be worse than the problem (if the planner ends up unable to
push the predicate down).
David J.
On Wednesday, March 21, 2018, Battuello, Louis
wrote:
> What permission is being violated at the schema level?
>
USAGE
https://www.postgresql.org/docs/10/static/sql-grant.html
David J.
jects
within a schema either own the schema too or have usage and create
permission thereon.
David J.
the constraint
on the supplied data.
And altering an owner of a table to one lacking usage and create
permissions on the schema is possible but unadvisible.
David J.
would have to assume that the answer is a
favorable yes.
David J.
into the public domain for anyone to
consume, regardless of whether they are/were a member of the mailing list
to which the email was addressed.
David J.
, I'd return an array if you need to accept the possibility of 1+
matches, and return an empty array for zero matches.
David J.
original no longer works.
>
Pretty sure on Ubuntu default the postgres O/S user authenticates itself
with PostgreSQL via "peer". The DB postgres user doesn't have a password.
David J.
pends greatly on your version.
https://www.postgresql.org/docs/10/static/parallel-query.html
David J.
llel-query-be-used.html
David J.
g queries
within those functions with the benefit of views is one possibility. Your
client access needs and general data model are necessary to choose a
preferred design.
David J.
nize an actual newline within the quoted field
as being valid data: but it doesn't appear that COPY is capable outputting
such.
David J.
users
in the games row. One of those users wins, and one of them loses. How you
have 2 winners in 1847 I cannot tell without seeing data. Why there is no
loser for 1926 is likewise a mystery.
David J.
e has a spare machine and a few TB lying around..
> .
>
The machine one tests their backup restoration process on should suffice
for this purpose...
David J.
s is what I would lean toward - with a data compression/decompression
step surrounding the network transfer.
Not sure exactly how it might fit in but don't forget about "DELETE FROM
... RETURNING *" and being able to place that into a CTE/WITH clause (same
goes for insert/update)
David J.
t a text value with runtime limitations.
David J.
rdering.
select * from unnest(ARRAY[3,6,4]::integer[]) with ordinality
Use LATERAL to move the unnest from the select-list section to the FROM
clause.
David J.
; This has become an issue as we started getting "FATAL: remaining
> connection slots are reserved for non-replication superuser connections"
>
>
Some live process somewhere seems to be keeping an open session with the
PostgreSQL service...
Long-lived non-idle statements would likely be waiting for a lock to be
released.
David J.
judgment on the site design until it is functioning.
This is more appropriately directed to pgsql-www
I cannot confirm the observed behavior only a couple minutes after
receiving your initial email.
David J.
On Wed, Apr 18, 2018 at 7:04 PM, Melvin Davidson
wrote:
>
> Normally, literals are inside the first quotes. IE: raise notice '
> blah_history.original_id' %', r;
>
But a compiler doesn't care about human concepts like "normally" - it just
cares abou
they include parameters, i.e.
> literal percent signs must always be doubled. This consistent behavior is
> necessary for using pgdb with wrappers like SQLAlchemy."
I'd hope not, as far as the driver is concerned the percent signs are text
content. It's plpgsql that is interpreting them directly in the server.
David J.
is report.
I'd rather have a developer spend time coding up having an FK constraint
define an AFTER STATEMENT trigger using a transition table and ensure that
all FK constraints remain enforced for all changed records. Correctly or
incorrectly written triggers do not have any liberty to violate FK
constraints and the fact that they can is reasonably considered by the user
base to be a bug.
David J.
he remote system is
> v8.4?
>
>
The last paragraph of the 9.6 pg_dump --jobs documentation covers pre-9.2
server dumps.
David J.
On Thursday, April 19, 2018, W. Trevor King wrote:
> Is there
> an idiomatic way to approach this problem?
>
>
I would use pl/pgsql as the language and build a query using a combination
of text literals and the format() function - invoking via pl/pgsql's
EXECUTE command.
David J.
OP - its just an unfortunate reality that this
isn't how things work today. Whether one can accept and work within this
reality is a personal decision.
This does reinforce that testing the restoration of ones backups is
important.
David J.
[...]
>
> ', condition);
>
> SELECT * FROM ancestors('WHERE item.id = 62324721');
>
Just keep in mind that this opens up a huge SQL-injection hole in your
database. Depending on how its called you might want to validation the
input text for both whitelist and blacklist items before executing it.
David J.
lumn list is what turned it into a "GROUP BY" query. When you
embedded the "AVG(score)" in a subquery the GROUP BY was limited to just
that subquery, and it had no other columns besides the aggregate and so
didn't require a GROUP BY clause.
David J.
ld be realized and if yes, how should I do it.
>
>
What you describe seems doable, at least to some degree. See the
documentation for:
CREATE TYPE
CREATE TABLE
CREATE FUNCTION
GRANT
David J.
ur(s) since it started and
that certain related records being no longer present doesn't detract from
the fact that they were present "back then" and thus represent valid data
at that point in time.
David J.
irst alphabetically. Ideally it would avoid
multi-column indexes since they are larger; and I believe that a float is
smaller than a uuid so not only alphabetically but the fpv index wins on
size too.
That the index is functional is immaterial here.
David J.
n easier-to-use ALTER TABLE RENAME. a
> memorable alternative/alias would be simply RENAME COLUMN columnName
> TO newColumnName.
>
I don't see us adding new syntax for this...
David J.
specifying any option for ssl. How is it doing ?
>
> psql (10.3 (Debian 10.3-1.pgdg90+1))
> SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
> bits: 256, compression: off)
>
psql uses SSL if it detects the possibility during the connection
handshake. This isn't RDS-specific.
https://www.postgresql.org/docs/10/static/ssl-tcp.html
David J.
On Friday, May 4, 2018, Havasvölgyi Ottó wrote:
> so that it matches (with less rank) even if one of its words match ?
>
That seems to be what "ts_rank" provides.
David J.
On Friday, May 4, 2018, Havasvölgyi Ottó wrote:
>
> Now I am thinking about splitting the input text myself to terms, then
> searching and ranking the documents for each term.
>
Maybe do: replace(input_text, ' ', ' | ')
David J.
t as well minimize
the pain and go recent and keep it longer. At this point I'd be asking
myself whether 9.6 or 10 is the better choice. Though with a third-party
application that runs on top of it I'd probably limit myself to the highest
release they purport to support.
David J.
, '', ''))
>
>
That code doesn't even run (extra comma after honor, not enough columns in
values). Provide working code and the definition of a_recipient.
In short, you've passed an empty string to an integer column, which doesn't
work. Supply null or pass a number.
David J.
ite/docs somewhere if you wish to find out more.
David J.
tgresql-cast/ but it gives me error `psycopg2.DataError: invalid
> input syntax for integer: ""`
>
>
You haven't asked a question and your statement is unsurprising to me. Are
you good now or do you still harbor confusion?
David J.
injection prevention. The use of "%s", which is typically a printf
construct and printf doesn't do that kind of thing, threw me.
David J.
fluent with this technique).
The more direct way to accomplish this is:
SELECT *
FROM func_call() AS (col1 text, col2 int, col3 date)
i.e., have the function return "SETOF record" and then specify the format
of the returned record when calling the function.
David J.
On Mon, May 7, 2018 at 2:35 PM, David Gauthier
wrote:
> Hi:
>
> At the psql prompt, I can do something like...
>"select \! id -nu"
> ...to get the uid of whoever's running psql.
>
> I want to be able to run a shell command like this from within a store
d out what names it does have. Or maybe
execute the query in something like psql and observe e column name there.
That said, by default the name of columns whose values are derived by a
single function call should be the name of the function. So "translate",
not "snumber" - the latter being consumed by the function. You can as use
"as " to give it a different fixed name and refer to that.
David J.
moving part to
deal with.
David J.
e contents of the portal being lost
due to the perform but that behavior isn't evident from the the section you
quote.
David J.
601 - 700 of 2414 matches
Mail list logo
