t to unexpected
databases via the pg_hba.conf file.
Why does userA need create database privileges?
You could just have them log into an admin database and run a function that
creates the database for them using function owner privileges then you can
add whatever special logic you want to that function.
David J.
On Friday, March 6, 2020, David G. Johnston
wrote:
> On Fri, Mar 6, 2020 at 4:28 PM Tiffany Thang
> wrote:
>
>> Hi,
>> Is there a way in PostgreSQL 12 to restrict user to creating a database
>> with a specific database name?
>>
>>
>
>> You cou
e it's not working. I'm doing anything wrong
>
NULLIF does the inverse of what you want - you expression returns null if
fieldna,e has a value of 0.
COALESCE is what you want.
Coalesce(fieldname, 0) — returns the first, non-null argument.
David J.
On Thursday, March 12, 2020, stan wrote:
>
> my $rv3 = spi_exec_query('$stmt');
> What am I doing wrong here?
>
Putting a variable name into a single-quoted string and expecting it to
resolve to the contents of said variable instead of being treated as a
literal.
David J.
On Thursday, March 12, 2020, Rob Sargent wrote:
>
>
> On Mar 12, 2020, at 7:44 PM, stan wrote:
>
> On Thu, Mar 12, 2020 at 06:37:02PM -0700, David G. Johnston wrote:
>
> On Thursday, March 12, 2020, stan wrote:
>
>
> my $rv3 = spi_exec_query('$s
rrectly.
The OP should (I think) be using a parameterized query instead of brute
force string manipulation since this code is a prone to exploit.
David J.
t either confirmed it is a bad idea nor the opposite.
>
There is a nice big bold warning callout in the documentation that covers
this explicitly.
https://www.postgresql.org/docs/12/manage-ag-tablespaces.html
David J.
model will be used; and without that information
useful comments pertaining to alternative implementations are difficult to
formulate.
David J.
On Thursday, March 19, 2020, Tom Lane wrote:
> Michael Lewis writes:
> > On Thu, Mar 19, 2020, 5:48 PM David G. Johnston <
> david.g.johns...@gmail.com>
> > wrote:
> >> However, one other consideration with sequences: do you care that
> >> PostgreS
>
The other implementation detail to remember is:
“Because nextval and setval calls are never rolled back, sequence objects
cannot be used if “gapless” assignment of sequence numbers is needed.”
David J.
existence at this moment”?
David J.
into place in the trigger function.
>
not tested but something like:
execute format('select nextval("obj_counter_%s_%s");', i_group, i_element)
into strict c_seqval;
or, more paranoidly:
execute format('select nextval(%I);', format('obj_counter_%s_%s', i_group,
i_element)) into strict c_seqval;
David J.
ile into a variable and then just perform a normal insert
(specifically, a VALUES (...), (...) variant). Since you can intermix psql
and SQL you basically output a bloody long script, that has memory issues
at scale - but you can divide and conquer - and then "psql --file
bloody_long_script_part_1_of_10.psql".
David J.
On Wednesday, March 25, 2020, Ekaterina Amez
wrote:
>
> What's wrong with the syntax? Or is not possible to make a script and I
> have to create a function to encapsulate my code?
>
This not working exactly the reason the “DO” command was created.
David J.
Ertek double precision;
>
> Do you know any other form?
>
>
No other form is specified in the documentation.
David J.
gt;
> Do you know any info about the background?
>
https://www.postgresql.org/docs/12/plpgsql-implementation.html#PLPGSQL-VAR-SUBST
David J.
nd undefined query) really think higher
percentages are worse or are you mis-communicating?
David J.
On Wednesday, April 1, 2020, David Gauthier
wrote:
> psql (9.6.7, server 11.3) on linux
>
> I want to do something like this
> (intentionally bad sql but will illustrate the need)
>
> select s.name,s.grade from students s where s.class='math'
> union
> sele
a
parts being populated by a query against information_schema or the
catalog. You’d need you something besdies pure sql (e.g., pl/pgsql) to do
this. You may find it easier to just brute force things.
David J.
XPRESSIONS-SUBSCRIPTS
update temprid set
ContactFirstName =(xpath(
'/E-Document/Document/DocumentParties/BuyerParty/ContactData/ContactFirstName/text()',x)
)[1]::text
David J.
ovides information
and is fairly cheap to capture and store. From the sounds of it you should
probably continue capturing the data but turn off the alert. That way if
there is a problem the data still exists to be considered. The metric
itself does not measure something of critical importance.
David J.
and explain
what may be wrong with that statement or portion of the file.
Removing stuff instead of just commenting it out is recommended.
David J.
eters (https://www.postgresql.org/docs/12/app-psql.html)
>
>
libpq understands URI connection strings:
https://www.postgresql.org/docs/12/libpq-connect.html#LIBPQ-CONNSTRING
David J.
gets enough information, or has better tooling available, to
handle this more gracefully.
JSON maybe...though at that point you may want to just consider changing
the model.
David J.
tps://www.postgresql.org/docs/12/functions-json.html
Then work out something that either works or gets you at least close. If
you are just close post the work done to date and any relevant points of
confusion.
I'll then likely be willing and able to fill in the missing gap(s) and
provide a relevant explanation.
You should formulate your query so that it doesn't require CREATE TABLE.
WITH vals (v) AS (VALUES (''::json)) SELECT vals.v FROM vals; makes
experimenting very easy.
Also, indicate which version of PostgreSQL you are working with.
David J.
good that actively running processes will keep going.
>
If you alter a password the old one is by definition no longer good...but
it doesn’t matter because authentication only happens once - during login.
David J.
On Thursday, April 9, 2020, David Gauthier wrote:
> psql (9.6.7, server 11.3) on linux
>
> In the copy/paste below, first 2 lines returned by a select on the view,
> why didn't it sort on start_datetime correctly ? I would think that the
> one started on 04-08 would come bef
Zone parameter, and is converted to UTC using
the offset for the timezone zone.
"""
https://www.postgresql.org/docs/12/datatype-datetime.html
David J.
at the space is going to be filled in again soon so it
doesn't normally make sense to "resize the array".
David J.
cation for the Bind Message in the documentation:
https://www.postgresql.org/docs/12/protocol-message-formats.html
Int16
The number of parameter values that follow
David J.
ld clear things up considerably.
https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW
David J.
On Thu, Apr 23, 2020 at 9:55 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Thu, Apr 23, 2020 at 9:37 AM Si Chen
> wrote:
>
>> Hello,
>>
>> I'm looking at my pg_stat_activity and trying to figure out what is
>> causing some of these pr
On Thu, Apr 23, 2020 at 9:58 AM Olivier Gautherot
wrote:
> Hi David,
>
> On Thu, Apr 23, 2020 at 6:55 PM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Thu, Apr 23, 2020 at 9:37 AM Si Chen
>> wrote:
>>
>>> Hello,
>>>
&g
database from
backup because there is a good chance this wasn't done properly and you
presently have a corrupted database on your hard drive.
David J.
On Thursday, April 23, 2020, David Gauthier
wrote:
> Hi:
>
> psql (9.6.7, server 11.3) on linux
>
> I have what appear to be a log of idle connections to my DB. Query of
> pg_stat_activity indicates well over half (127/206) are like this...
>
>
> dvdb=# select s
System from Time Machine/(backup).
>
>
And was that Time Machine backup made while the server was offline? If
not, and you didn't take any explicit steps to backup and restore the
database itself, then your database may be corrupted and thus unable to
boot. The log file should indicate whether that is the case.
David J.
On Thu, Apr 23, 2020 at 11:52 AM David Gauthier
wrote:
> Thanks!
> And an example of connection pooling is pgBouncer ?
>
>>
>>
It does describe itself as being a "Lightweight connection pooler for
PostgreSQL" ...
https://www.pgbouncer.org/
David J.
of this line “*SSL connection (protocol:
> TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression:
> off)”?*
>
>
> Specifically, no. But passing "--quiet" as a command line option to psql
should suppress it, as well as some other stuff.
David J.
Please don't top-post; and this is a fairly rude hijack posting given that
you already have a thread going, from today no less, where you've basically
asked this very same question.
On Thu, Apr 23, 2020 at 2:18 PM Si Chen
wrote:
> Hello David & David,
>
> I have a simi
;
>
The "sudo" in Rob's answer wasn't optional. Attempting to use sudo should
require the OS Admin Password
David J.
On Fri, Apr 24, 2020 at 8:55 AM Steve Clark
wrote:
> Hello,
>
> I am using psql to copy data extracted from an InfluxDB in csv format into
> postgresql.
> I have a key field on the time field which I have defined as a bigint
> since the time I get
> from InfluxDB is an epoch time.
>
> My question
On Thursday, April 16, 2020, Andreas Andreakis
wrote:
>
>
> Does this allow to detect column renames ?
>
“The database schema and DDL commands are not replicated“
https://www.postgresql.org/docs/current/logical-replication-restrictions.html
David J.
Please don’t top-post.
On Monday, April 27, 2020, Andreas Andreakis
wrote:
> Thx for replying David.
>
> According to https://www.postgresql.org/docs/10/protocol-logicalrep-
> message-formats.html it looks like certain schema information is embedded
> via pgoutput such as column
On Monday, April 27, 2020, David G. Johnston
wrote:.
>
>
> Are there plans to add comprehensive schema change detection abilities via
>> logical replication ? either by embedding more information into pgoutput or
>> perhaps by embedding the schema DDLs ?
>>
>
>
On Wed, Apr 29, 2020 at 8:17 AM David Gauthier
wrote:
>
> Check constraints:
> "dvm_events_status_check" CHECK (status::text = ANY
> (ARRAY['passed'::character varying, 'failed'::character varying,
> NULL::character varying]::text[]))
>
tation does describe the
step of porting extension libraries. It says you need to do that before
you run pg_upgrade and ensure that whatever is being installed onto the new
server is binary compatible. It even says explicitly that pg_upgrade
cannot do this for you.
https://www.postgresql.org/docs/12/pgupgrade.html
David J.
error at the first '(' around 'report ->>'.
>
Documentation says constraints must reference column names - so no. You
can probably get what you are after by directly creating a unique index
though - those allow expressions.
David J.
ective of default_statistics_target, is probably a good thing to try.
David J.
at seems to be a superior alternative.
David J.
>
>
pi.app_id = pa.app_id AND
pi.user_id = '317079413683604'
)
ORDER BY
pa.process_instance_id,
pa.created limit 50;
I'm unsure exactly how this will impact the plan choice but it should be an
improvement, and in any case more correctly defines what it is you are
looking for.
David J.
> the versions?
>
You can peruse the release notes online but regardless the project policy
is that you should never not apply a minor release update (though we also
don't use 11.6-2 version numbering...).
David J.
onfig-replication.html#RUNTIME-CONFIG-REPLICATION-SENDER
David J.
of joining), maybe, does it
take the effort of constructing a minimally necessary output column set.
David J.
ted should define the traversal order...)
so that the output of the nested loop ends up being (max 2 x 50) 100
entries which are then sorted and only the top 50 returned.
Whether the executor could but isn't doing that here or isn't programmed to
do that (or my logic is totally off) I do not know.
David J.
On Fri, May 8, 2020 at 9:26 AM David Gauthier
wrote:
> psql (9.6.0, server 11.3) on linux
>
> Looking for ideas. I want a trigger to...
> 1) compose an html report based on DB content
> 2) email the report to a dist list (dl = value of a table column)
>
> If this will inv
new table and
dropping the old one would be the best solution when taken in isolation.
David J.
ry SQL
command, and then must periodically call the function PQnotifies to find
out whether any notification events have been received.".
IOW, the interface for the client is still a polling interface its just
that with LISTEN the event is transient and in-memory only (on the server)
and thus h
me on the
table holding the PK half of the relationship.
David J.
be stored (OP has not specified but think using an integer
value of someones social security number as a key). The FK side of the
relationship equality has the same illegal data values problem and need to
be changed too.
David J.
On Fri, May 8, 2020 at 1:51 PM Rob Sargent wrote:
>
> On May 8, 2020, at 2:43 PM, David G. Johnston
> wrote:
>
> On Fri, May 8, 2020 at 1:41 PM Rob Sargent wrote:
>
>> My understanding is the keys in the info_table need to change. That
>> causes the very expensi
te to a point sometime before the target
time and then restore until you attain the target time.
David J.
e table constraint (aside from an artificial primary key).
Then, if you can define a problem where you feel having a unique table
constraint over the field is the correct solution - especially given the
fact that you can have missing data in the relevant fields - you should
post the problem and take suggestions on ways to solve it.
David J.
on 9.6, 10, and 11 to see if it is indeed new to
12 or at least appears on other versions?
David J.
uation and try v12
David J.
t to remove the pg_upgrade from the equation and try v12
David J.
On Monday, May 11, 2020, David G. Johnston
wrote:
> Repost, edited subject by mistake...
>
> On Monday, May 11, 2020, Tory M Blue wrote:
>>
>> And just to repeat. Same exact hardware, same kernel, nothing more than
>> installing the latest postgres12, copying my
org/message-id/1375925710.17807.
> 13.camel%40vanquo.pezone.net
Yeah, because usually one just writes your example:
Select sum(“v”) from (select 0 as “v”) as t1 group by “v”;
> I also posted on DBA.StackExchange
> https://dba.stackexchange.com/questions/266988/why-is-it-
> that-aggregate-functions-are-not-allowed-in-from-clause-of-their-own
>
>
I did not.
David J.
database they must use a physical backup of the
original database as a starting point. To use a logically restored
database target would require logical replication.
David J.
On Fri, May 15, 2020 at 8:27 AM Michael Lewis wrote:
> Just wonder, have you compared these on the two servers?
>
> select * from pg_settings where name = 'DateStyle';
>
The OP is using to_timestamp, the DateStyle setting is immaterial.
David J.
match space template markers with non-space input, skipping
the template position altogether without moving along the input string.
You will want to change your template to use "T" which more closely matches
the input data anwyay.
Order you can, and probably should, just stop using to_timestamp and do
("JS->>'mydate)::timestamptz (which has the added benefit of keeping the
timezone information).
David J.
y
to its parse result earlier. For pl/pgsql none of that happens until the
function is called. Because of this pl/pgsql allows for ambiguous sql text
to exist and be concretely resolved during execution while SQL does not.
David J.
test schema in the search_path.
David J.
t. "TEXT" is not a useful
answer.
If the raw data is too large no index is going to be "best" - as the hint
suggests you either need to drop the idea of indexing the column altogether
or apply some function to the raw data and then index the result.
David J.
On Thu, May 21, 2020 at 7:45 AM postgann2020 s
wrote:
> >And what type of data exactly are we talking about. ==> Column is
> stroing GIS data.
>
GIS data isn't really TEXT and isn't a core datatype of PostgreSQL so this
is maybe better posted to the PostGIS community directly...
David J.
;t have any other place where
that gets saved I would save it to a table first then convert it to the
final structured tables.
My current setup is using "jq" to perform an intermediate transform of the
source json to table-specific files and then using psql and jsonb_to_record
insert the
or a
> 'native' Windows instance. Just trying to get confirmation.
>
Nothing in the OP's text suggests a different server is involved - rather
same server but LIKE vs equals.
The LIKE query probably doesn't use an index and thus finds the relevant
data via sequential scan and equality checks on each record.
David J.
o be a false negative - assume something
will break or simply give incorrect results.
David J.
help us to
> improve the performance of the query?
>
I have no idea what your are trying to get at here. You should try
providing SQL that actually runs. Though at first glance it seems quite
probable your are doing useless work anyway.
David J.
e suggest is there any other ways to validate this kind of queries
> which will improve the overall performance.
>
Abandon procedural logic and embrace the declarative set oriented nature of
SQL.
David J.
nstead of the SQL standard
information_schema view. The later doesn’t provide detail of
PostgreSQL-specific features by definition.
David J.
don't provide you what you need you need
to fall-back to the system tables listed in [2].
[1] https://www.postgresql.org/docs/12/views-overview.html
[2] https://www.postgresql.org/docs/12/catalogs-overview.html
David J.
gt; how.
>
>
If a left join doesn’t give you the answer you want you should probably
provide exact input and output data that you are working with/toward.
David J.
. You might also consider just outputting one
row per person and output the related phone numbers using
string_agg(phone.number, E'\n')
David J.
this? I would
expect that you could store the offline files anywhere. As long as the
architecture your original server is on and the one you are restoring to
are the same the restored server should work. They are just bytes until a
server interprets them, no?
David J.
needed
> 2. Hot standby: User databases in both clusters contain same data.
>
>
Why are you spending so much effort on this Window/Linux hybrid setup? Get
yourself another Linux server and setup physical replication. It sounds
like it will exactly meet your requirements and you will waste more time
and money working out alternatives than the server would cost.
David J.
se proper. i.e., store previously signed data into a normal text
or bytea field.
That said there is:
https://www.postgresql.org/docs/12/pgcrypto.html#id-1.11.7.34.7
David J.
ork in your specific situation is impossible to say since
you provide zero information about your environment.
David J.
On Wed, May 27, 2020 at 9:31 AM postgann2020 s
wrote:
> Thanks, David,
>
> Please find the environment details.
>
> Environment:
> PROD:
> OS: RHEL 7.1
> Postgres: 9.5.15
>
> Staging:
> OS: RHEL 7.1
> Postgres: 9.5.15
>
Ok...not particularly helpful thou
lts are always the same regardless of whether the table has been
> ANALYZED yet.
>
> Could anyone share knowledge about why this is happening?
>
A likely scenario is you are missing an ORDER BY in a location where you
are depending on deterministic row ordering and its changing out from
underneath you.
David J.
re
tested it is more likely they were tested against 9.0.23 as it is years
more current.
David J.
in order to combine multiple inputs onto
the same table.
David J.
% ^ & | ` ?
For example, @- is an allowed operator name, but *- is not. This
restriction allows PostgreSQL to parse SQL-compliant queries without
requiring spaces between tokens.
David J.
qs for this feature also have other concrete benefits that are worth
working toward, and in the end the sum of those makes cross-database
queries a relatively simple matter, I would entertain putting in the last
10% of effort to become standard compliant.
David J.
nk you.
>>
>
https://yum.postgresql.org/news-newreporpmsreleased.php
David J.
On Monday, June 8, 2020, Matthias Apitz wrote:
>
> Can some kind soul help me with doing a test for the existence of the
> table to avoid the error message about non existing relation?
>
https://www.postgresql.org/docs/12/catalogs-overview.html
David J.
On Mon, Jun 8, 2020 at 2:57 PM Alistair Johnson wrote:
>
> RETURN EXTRACT(datepart FROM end - start);
> Any ideas? Is this even possible?
>
Use the "date_part" function.
David J.
R as a custom type
value without single quotes surrounding it
If that is the question the answer is no. The only type literals that can
be written without single quotes are numbers.
The parsing of SQL can handle some standard mandated non-quoted constants
but they are basically keywords, not values.
David J.
On Mon, Jun 8, 2020 at 5:06 PM Martin Gainty wrote:
> CREATE OR REPLACE FUNCTION DATETIME_DIFF(end TIMESTAMP(3), start
> TIMESTAMP(3), datepart UNIT) RETURNS DOUBLE PRECISION AS $$
>
Duplicate email from account (same sender) - already answered on the
original/correct thread.
David J.
bpq consults environment variables to allow for user customization.
David J.
om a shell script or
> SQL function in order to see the output as each index is checked?
>
You could wrap the function call in a custom plpgsql function (or just do a
plpgsql loop) and side channel output via notice but i’d probably just do a
shell script wrapper absent any other constraint.
David J.
901 - 1000 of 2436 matches
Mail list logo
