On Tue, Nov 8, 2022 at 6:27 PM David Gauthier wrote:
>
>
> dvdb=# insert into testarr (arr) values ('{"hijk\"lmnop"}');
>
This is (one of) the correct ways to formulate what you want.
> INSERT 0 1
> dvd
doing anything useful by revoking all default privileges its
mere presence produces no harm. If you do remove connect, then the only
people who could assume that role would be members who themselves have
connect privilege. It is seemingly pointless to prohibit them from
assuming any of the roles they are a member of on the basis of which
database they are in. In short, yes, the permissions model could be made
more nuanced than its present design. But it isn't, it isn't documented to
be, and your assuming that connect implies non-assumability doesn't seem to
stem from anything the documentation actually says.
David J.
ases, can
assume any roles the user name of the credentials they used are a member
of. Whether that is actually useful or not depends upon grants. But in no
case can you prevent them from, say, examining the database catalog tables,
or executing at least some limited SQL.
David J.
ver (V14) or will I have to also install V14 in the office and
> dump/restore with that?
>
>
https://www.postgresql.org/docs/current/app-pgdump.html
You should read the description and notes sections for how this command
works and its capabilities and potential limitations.
David J.
amed
superusers as part of the final policy. Especially since any policy of
this requires not only discussion of PostgreSQL itself but operation
systems, configuration management, etc
David J.
t I cannot wholly discredit as bad, but that don't
fit into my generality, get the "if the specific circumstances warrant it"
treatment. My own presuppositions ultimately should get the same treatment
by whomever is implementing such policies.
David J.
e regime of O/S
> users and file privileges. Maybe this is the key to the effectively
> tamper-proof implementation of the scheme that David recommends. (Having
> said this, there's always the "set role" backdoor.)
>
If you are worried about back-doors here you gave the
On Mon, Nov 21, 2022 at 5:30 PM Adrian Klaver
wrote:
> On 11/21/22 15:05, Bryn Llewellyn wrote:
> >
> > In fact, David Johnston did unequivocally challenge my strawman a couple
> of turns back, thus:
> >
>
>
> And the equivocal additions later in the pos
1;
There is no difference with replacing 1 with a composite type and the
SELECT subquery with VALUES instead.
Your "VALUES" is just a scalar subquery expression that, if it indeed
produces a single row and column, can be compared to any other scalar value
(if it produces more than one row you will get an error - try it).
David J.
vious month it shouldn't be calculate on next months
>
>
You need a subquery to compute the month in which each wallet_id should
appear (group by wallet_id with min(date) probably), then you can group on
the min(date) column and count the wallets.
David J.
onnected to.
The catalogs are not information_schema.
> However, I think I can try "SELECT 1 FROM .pg_indexes...".
> Will this work?
>
>
What is that even supposed to mean? It also seems simple enough to just do
that asking "will this work" is a waste of time. Just try it.
David J.
>
The point was the terminology - write "database" and "cluster" instead of
"catalog" and whatever (if anything) the SQL Standard has in place for "a
group of databases".
David J.
act, while true, is unhelpful for this specific question.
There is a nice big caution regarding the default read committed isolation
mode, order by, and for update, in the documentation, but I cannot work out
exactly why this example seems to be triggering it.
https://www.postgresql.org/docs/current/sql-select.html
David J.
anging all the many, many, places in the
documentation where just "database superuser' is used - and so we've
updated the glossary to be just that.
David J.
be
updated due to, e.g., renaming of objects. The text source of the original
command would be unable to be updated in the same way and so it is possible
the text and the parsed contents would diverge over time, which is a
situation we do not want to have.
David J.
uppose knowledge is helpful
but security by obscurity isn't real security.
David J.
n you've provided is not a productive activity.
David J.
>
RegExp by itself cannot do this. You have to match all parts of the input
into different capturing groups, then use lower() combined with format() to
build a new string. Putting the capturing groups into an array is the most
useful option.
David J.
M test;
>
A bit too inefficient for my taste.
I was describing the following:
with parts as materialized (
select regexp_match(
'abc_def_ghi',
'^([^_]*_)([^_]*_)([^_]*)$') as part_array
)
select format(
'%s%s%s',
part_array[1],
upper(part_array[2]),
part_array[3])
from parts;
David J.
ee/master/src/tools/msvc
pgbison.pl internal tool to process .y files using bison
pgflex.pl internal tool to process .l files using flex
David J.
vided functions that have been
programmed with knowledge of how the system works.
https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE
select has_database_privilege(0,current_database(),'CONNECT');
David J.
ion 5.7 for more information about privileges.)
In these functions, the user whose privileges are being inquired about can
be specified by name or by OID (pg_authid.oid), or if the name is given as
public then the privileges of the PUBLIC pseudo-role are checked.
https://www.postgresql.org/docs/current/functions-info.html
I'm not sure where I picked up the comment about 0 working but since
"public" works and is documented that implementation detail need not be
discoverable.
David J.
On Sat, Dec 17, 2022 at 4:55 PM Michael Arnold wrote:
> How do I interpret the 4 bytes of postgresql 'date' value?
>
See date2j and j2date in datetime.c
timestamp.h has the relevant constant Tom refers to (POSTGRES_EPOCH_JDATE)
David J.
On Sun, Dec 25, 2022 at 12:25 AM David Goldsmith
wrote:
> (
>
>
> ''9adfe0ee-af21-4ec7-a466-c89bbfa0f750''
>
> ,
> ''8714b2e3-d7ba-4494-a3ed-99c6d3aa2a9c''
>
>
ND pg_get_expr(d.adbin, d.adrelid) = 'now()'
>
>
>
Extend the query so all inputs needed to manually write an ALTER TABLE
command (chiefly, you need textual names, not oids). Then use format() to
actually write those commands using the query as input. You can use psql
\gexec to actually execute those queries, though other options exist
depending on what tools you are comfortable with).
David J.
ific features), or a single backslash
command. Thus you cannot mix SQL and psql meta-commands within a -c option.
Thus any meta-command that interacts with server-parsed SQL is rendered
useless in -c
David J.
OM employee WHERE ssn LIKE :'%v_ssn%';
>
>
Two options:
format('%%%s%%', :'v_ssn')
'%' || :'v_ssn' || '%'
David J.
Please don't top-post.
On Fri, Jan 13, 2023 at 10:08 AM Adam Scott wrote:
> Do you mean like this?
>
> postgres=# \set v_embed %:v_ssn%
> postgres=# \echo :v_embed
>
> test=# \set v_like %:v_ssn%
>> test=# \echo :v_like
>>
>>
Aside from the names those look identical...
David J.
microseconds what is being
requested is to produce:
24 months 1.5 days 15123457 microseconds (or whatever the values stored in
those three positions is...)
David J.
The materialized view itself bloats. The temp tables are removed
immediately (the “drop table” docs don’t say this explicitly though it the
most logical behavior and implied by the fact it takes an access exclusive
lock).
David J.
ange.com/
> questions/219079/bloat-on-materialized-views Unfortunately nobody
> answered...
>
>
The definition of bloat is a deleted row. Bloat can be reduced by
subsequent row insertions.
David J.
ED view tbtmv;
> SELECT * FROM pgstattuple('tbtmv'); -no dead tuples count.
>
You didn’t specify concurrently so the merge method you quoted is not being
used.
David J.
nd the
fact it cannot be found, what are the next closest function signatures that
are present".
David J.
se" to
> 1663 (or whatever the value is)?
>
>>
>>
The system itself just uses an IF block...
https://github.com/postgres/postgres/search?q=DEFAULTTABLESPACE_OID
David J.
to stdout. If you want to format your output with stuff other than query
results it provides \echo
David J.
On Tue, Jan 17, 2023 at 4:07 PM raf wrote:
> On Tue, Jan 17, 2023 at 02:22:22PM -0700, "David G. Johnston" <
> david.g.johns...@gmail.com> wrote:
>
> > On Tue, Jan 17, 2023 at 1:48 PM Ron wrote:
> >
> > >
> > > White space can of course m
s are not considered
> equal, unless NULLS NOT DISTINCT is specified.
>
>
I think saying that NULLs are distinct by default is the better way to
present this. The documentation disagrees on the verbiage but both state
the same truth; the feature matrix page needs to be fixed.
David J.
On Tue, Jan 24, 2023 at 12:30 PM kaido vaikla
wrote:
> Hi
> Take a look at parameter
> log_line_prefix
>
>>
>>
Really? Care to explain in more detail. I wouldn't expect that setting to
have anything to do with WAL.
David J.
oup by e.ma order by mates
> )
> select mates, count(*)
> from husb
> group by mates order by mates desc;
>
> Your "order by mates" in the CTE is totally pointless and wasting
resources.
David J.
On Fri, Jan 27, 2023 at 2:25 PM Rob Sargent wrote:
> On 1/27/23 14:20, David G. Johnston wrote:
>
> On Fri, Jan 27, 2023 at 1:59 PM Rob Sargent wrote:
>
>> I'm trying to craft SQL to invoke a sequence nextval once per grouped
>> value.
>>
>>
> Thi
60107032927.GT3685%40tamriel.snowman.net#6d9e59a0d052e7bdccd5a6c4e7a44a3f
David J.
on?
>
https://github.com/postgres/postgres/blob/master/src/backend/utils/misc/timeout.c
David J.
ou most likely have a large
bloating problem going on. Seems like it is time to vacuum full.
David J.
system decides which pages to
fetch before checking to see if they are already in shared buffers (as
opposed to returning the ones in shared buffers first then fetching more if
needed)?
David J.
w)
>
>
>
This wasn’t the question though. Can the second session see the inserted
row before you cancel the insert that is waiting for sync ack?
Supposedly it can (not able to test myself). Basically, the primary waits
to make the local transaction visible until either sync ack or until the
wait for sync ack is cancelled. It doesn’t make sense to make it visible
while waiting for sync ack since that would defeat the very behavior sync
ack provides for.
David J.
On Wed, Feb 1, 2023 at 11:15 AM Dimitrios Apostolou wrote:
> On Tue, 31 Jan 2023, David G. Johnston wrote:
> >
> > It feels like there is room for improvement here using table statistics
> and the visibility map to significantly reduce the number of pages
> retrieved that tu
ad it would remove page 21 from the end of the
relation since that wouldn't affect "sequential and gapless".
David J.
able. It isn’t “supported”
but is allowed for technical convenience, hence the decision to avoid
trying to communicate this fact in the syntax diagram.
David J.
e how optional elements are introduced so
while those two options exists neither are required.
David J.
LA violation and little chance for any solution to be back-patched since
the issue is lack of good existing technical options.
https://www.postgresql.org/message-id/1990141.1638388378%40sss.pgh.pa.us
https://www.postgresql.org/message-id/flat/15533-ac9506ee433a0d18%40postgresql.org
David J.
ecution plan, I would greatly appreciate your insights and suggestions.
>
>
https://www.postgresql.org/docs/current/auto-explain.html
David J.
d string isn't
the one that is producing the error. Which suggests that maybe you have a
string somewhere that is not written correctly that need to be fixed so it
doesn't produce this error.
David J.
g_stat_user_indexes`
> view.
>
>
The select produces the index scan, not the insert. The insert doesn’t
scan, it populates and aborts if that population fails. It doesn’t check
first.
David J.
ge from CREATE UNLOGGED TABLE.
I don't have any particular suggestion for PostgreSQL cron extensions but
I'm fully on board with the current division of responsibilities here.
Such a user-space application isn't something that the core developers
should be worried about nor does such a project really want to be tied to
the release cadence that the server is restricted to.
David J.
binary part but there are rules for how to serialize to
text both composites and arrays, and quite probably libpq provides
functions for this already though i've never used it raw.
David J.
On Thu, Feb 9, 2023 at 9:09 AM Dominique Devienne
wrote:
> On Thu, Feb 9, 2023 at 5:03 PM Dominique Devienne
> wrote:
>
>> On Thu, Feb 9, 2023 at 4:51 PM David G. Johnston <
>> david.g.johns...@gmail.com> wrote:
>>
>>> On Thu, Feb 9, 2023 at 8:41 AM D
ne'::text), (2, 'two'::text));
> ?column?
> --
> t
> (1 row)
>
>
But you cannot write the right-side of the IN as a single parameter which
seems to be the primary constraint trying to be conformed to.
David J.
and
what their permissions are. I strongly suspect your server has been hacked
and you'll find roles there that don't belong, and the postgres role
stripped of its superuser status.
David J.
e case
for the PK) and use both `id` clauses in the index condition?"
David J.
UNNEST function does to produce
the output row numbers external to the function body and signature.
David J.
do have access to cost parameters, and
potentially other planner toggles if you've truly run into an intractable
problem.
David J.
of the relationship
> between the two tables:
>
> 1) The actual FK relationship.
>
> 2) What "...preserve the severed reference (by natural key, i.e. its
> name)" means?
>
> 3) What information will be used to reconnect the child rows to the
> parent rows?
>
>
Maybe the OP should be using ON DELETE SET NULL instead of CASCADE?
David J.
to design the new system to behave in a manner similar
to the legacy system for the stuff they share in common. You can then have
a uni-directional trigger going from legacy to modern.
David J.
vacuum full have any limitation like that?
>
No, vacuum full is not designed to destroy xml data that has been stored in
the database.
David J.
ROLLBACK before ending the transaction that it implicitly started.
David J.
On Mon, Feb 20, 2023 at 9:57 AM Marc Millas wrote:
>
> select pg_reload_conf();
> t
>
> So, I am perplexed: what pg_reload_conf() is doing/not doing ?
>
It is sending a signal and stating success that said signal was sent.
David J.
atements are executed as a single
transaction
So in effect bundling within the Simple Query Protocol has a similar effect
to wrapping all statements in a begin/commit pair.
David J.
something pointless
and the other just does the pointless thing without complaint.
Frankly, the non-default versions are simply the one and only way you can
see what the current value is. That you can then SET them to change it is
I suppose convenient but since there is an actual SQL command to do the
same one should use that command, not the setting.
David J.
On Fri, Feb 24, 2023 at 1:47 PM Rob Sargent wrote:
>
> Why is the last one headed "ascii" and not "bit"?
>
Because a function name is more likely to be a useful label than a data
type.
David J.
e
function.
CREATE OR REPLACE FUNCTION api."post_publish_Roster"(
)
RETURNS void
LANGUAGE 'sql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
David J.
.e., get rid of
the ORDER BY also. Maybe this belongs in an application layer with tooling
that already provides this capability.
David J.
On Mon, Feb 27, 2023 at 5:22 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
>
> Lastly, if you do need to care about normalizing the output of JSON you
> should consider writing a function that takes arbitrary json input and
> reformats it, rather than trying to
c)
from subtwo group by base_id) as st (base_id, subtwo_arr)
using (base_id)
I used a mix of forms in the two joins, and there are other variants, but
the basic concept holds - produce single rows in subqueries then join those
various single rows together to produce your desired json output.
David J.
would assume the indexes, which are indeed a fundamental part
of the table, would be moved along with the table. Experiment if you want
a more concrete answer.
But it doesn't make sense to have these things be in different schemas than
the tables they modify so it all makes sense from a design standpoint.
David J.
ou aggregate the data that are in
those groups is determined by which function you call. Sure, a function
like "count()" produces a single number, but there are other functions.
Even a whole documentation section listing them.
https://www.postgresql.org/docs/current/functions-aggregate.html
David J.
riting it using "format()"
and then substituting the values into the query via placeholders.
Roughly like:
SELECT * FROM crosstab(format('SELECT * FROM %I WHERE %I = %L, :'tbl_name',
:'col_name', :'compare_value'));
David J.
;, ...). The table_func
itself would then just use pl/pgsql variables (or SQL ones...) in a normal
query instead of a dynamic query (presuming you are only substituting
values anyway).
David J.
ot;FileKey" LIKE '%1317'
>
> I'm stumped. Any guesses?
>
Agreed, what you've shown doesn't make sense.
Try:
EXPLAIN ANALYZE
REINDEX (on any underlying table indexes)
Show the version you are running.
David J.
..FileKey is a hyphenated
concatenation of the ORDER Id and the ORDER ITEM Id, while OrderItemID is
just the later - and the suffix of the former matches the later.
David J.
On Mon, Mar 6, 2023 at 7:51 PM David Rowley wrote:
> On Tue, 7 Mar 2023 at 12:40, Tom Lane wrote:
> >
> > Ben Clements writes:
> > > As shown above, the following calculated column can bring in the city
> name,
> > > even though the city name isn't
d be overly worried about.
Actually making DDL changes of this nature should be rare if not
forbidden. Once live on-the-fly column type changes just shouldn't happen
so having a plan in place that accommodates them is adding cost for no real
benefit.
David J.
;
>
No. If you simply "ADD COLUMN" to an existing table the "terminate all
client-sessions" action is excessive, IMO.
David J.
(adding back the list)
On Tue, Mar 7, 2023 at 8:24 PM David G. Johnston
wrote:
> On Tue, Mar 7, 2023 at 7:54 PM Bryn Llewellyn wrote:
>
>>
>>
> This is what I expected actually, though I can't point to exactly why.
>
>>
>> Where can I read what I need i
Forwarding this to the list; Note the potential bug found at the end. My
actual follow-on reply notes the lack of documentation regarding the
composite cache-checking behavior (relative to the non-composite situation)
-- Forwarded message -
From: David G. Johnston
Date: Tue, Mar
On Tue, Mar 7, 2023 at 9:49 PM Tom Lane wrote:
> "David G. Johnston" writes:
> > So I found where this difference in behavior is at least explicitly
> noted:
>
> >/*
> > * If it's a named composite type (or domain over one), find the typcache
> >
it's worth trying to reason when it might be safe to avoid
> this re-start?
>
True. I was a bit hasty in forming an opinion on an operational aspect
like that. That just isn't something I typically think about.
David J.
On Wed, Mar 8, 2023 at 5:41 PM Bryn Llewellyn wrote:
>
> select (((17, 42)::s.t2)::text)::s.t2 into r2;
>
> then I'm back to the same 22P02 error:
>
> invalid input syntax for type integer: "(17,42)"
>
>
Single quotes. SELECT '(17,42)'::s.t2;
David J.
Hence the second
example here[1]
[1]
https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT
[2]
https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW
David J.
gt;
>
>
> Is there a way pass a list of names and types to crosstab (‘query’) as
> (<‘type list as strings in array’> ) ?
>
> In other words,
>
> Can I pass an array to crosstab row labels
>
>
No, you cannot change query structure at execution time in SQL.
David J.
ist.
> select (a.c1).a1, (a.c1).a2 into r from s.t as a where a.k = 1;
> select a.c2 into arrfrom s.t as a where a.k = 1;
>
Yeah, I can see this as a natural consequence of the "column per field"
behavior decision. Maybe it should be covered better in the docs? Seems
like an unfortunate corner-case annoyance seldom encountered due to the
general disuse of composite types.
> for the_row in (select a.c1, a.c2 from s.t as a order by a.k) loop
> z := the_row.c1::text||' / '||the_row.c2::text;
> return next;
> end loop;
>
Doesn't seem like a terrible work-around even in the single-row case...
David J.
ns. (I'm assuming here that no particular
> schema-object can have a row both in pg_class and pg_type.)
You assume incorrectly.
>
> Finally, what terms of art do PG experts use to distinguish between
> single-valued data types like "integer", "text", "boolean" and so on and
> multi--valued data types like "array", "row", "record", and the result of
> "create type… as (…)"?
>
Scalar; or if you go by the documentation, base type.
> They respectively create a composite type, an enum type, a range type, a
base type, or a shell type.
Also
> A composite type is essentially the same as the row type of a table,
You can also read the description for pg_type:
https://www.postgresql.org/docs/current/catalog-pg-type.html
In particular:
> Base types and enum types (scalar types) are created with CREATE TYPE
and
> A composite type is automatically created for each table in the database
David J.
can sometimes help make a
function independent of changes to the definition of a table.
I mean, while it is indeed "independent of changes to the definition of a
table" so does simply writing "text"...the %type syntax seems like it
should follow the changes of the definition of a table...
David J.
/www.reddit.com/r/PostgreSQL/comments/11pu7vp/numeric_type_division_weirdness/
Thanks!
David J.
the commands you are
running. Showing that psql itself works on both running clusters would be
a good start. Show inputs, not just outputs those inputs creates.
David J.
e is rightfully complaining.
David J.
rest of us?
Anyway, you had to have done more than that to use those other roles to
appear in the new cluster.
David J.
On Mon, Mar 20, 2023 at 9:13 AM DAVID ROTH wrote:
> Is there any good reference to explain the best usage of each of these
> structures.
> I am coming from Oracle. What is the best analog to Oracle's "user".
>
A schema is a namespace mechanism for objects. It h
use "COPY ... TO
STDOUT" directly instead.
All of this is discussed under "\copy" in the documentation (including
notes).
https://www.postgresql.org/docs/current/app-psql.html
David J.
where it "should" be.
>
>
If that would give you what you need then just define the column as text
initially, load the data, then do an ALTER COLUMN ... ALTER TYPE to change
the column type to timestamptz in place, with the conversion done via USING.
David J.
On Wed, Mar 29, 2023 at 7:39 PM Ron wrote:
> Something like this?
>
> ALTER TABLE foo
> ALTER COLUMN update_ts TIMESTAMP WITHOUT TIME ZONE
> USING to_timestamp(update_ts, 'MMDDHH24miSSMS');
>
> That would definitely minimize the possibility of errors.
>
Yes.
David J.
1401 - 1500 of 2437 matches
Mail list logo
