Re: How to update upper-bound of tstzrange ?
Thanks all for your answers ! Much appreciated.
Sent with Proton Mail secure email.
On Tuesday, 21 May 2024 at 11:02, Laurenz Albe wrote:
> On Mon, 2024-05-20 at 13:56 +0200, Erik Wienhold wrote:
>
> > On 2024-05-20 12:30 +0200, Laura Smith wrote:
> >
> > > Could someone kindly help me out with the correct syntax ?
> > >
> > > My first thought was the below but that doesn't work:
> > >
> > > update foo set upper(bar_times)=upper(bar_times)+interval '1' hour where
> > > bar_id='abc';
> > > ERROR: syntax error at or near "("
> > > LINE 1: update event_sessions set upper(bar_times)=upper(bar_ti...
> >
> > Use the constructor function:
> >
> > UPDATE foo SET bar_times = tstzrange(lower(bar_times), upper(bar_times) +
> > interval '1' hour);
> >
> > But this does not preserve the inclusivity/exclusivity of bounds from
> > the input range, so you may have to pass in the third argument as well.
> >
> > https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-CONSTRUCT
>
>
> If you need to preserve the information whether the upper and lower bounds
> are inclusive or not, you could
>
> UPDATE foo
> SET bar_times = tstzrange(
> lower(bar_times),
> upper (bar_times) + INTERVAL '1 hour',
> CASE WHEN lower_inc(bar_times) THEN '[' ELSE '(' END ||
> CASE WHEN upper_inc(bar_times) THEN ']' ELSE ')' END
> )
> WHERE ...
>
> Yours,
> Laurenz Albe
How to update upper-bound of tstzrange ?
Could someone kindly help me out with the correct syntax ?
My first thought was the below but that doesn't work:
update foo set upper(bar_times)=upper(bar_times)+interval '1' hour where
bar_id='abc';
ERROR: syntax error at or near "("
LINE 1: update event_sessions set upper(bar_times)=upper(bar_ti...
Obsolete Linux downloads (Debian) instructions
Hi
Who do I have to badger to get the obsolete and frankly dangerous Debian repo
instructions fixed @ https://www.postgresql.org/download/linux/debian/ ?
The manner proposed is really "not the done thing" in 2024 and it has been
explicitly obsoleted by Debian so the project really should not be promoting it
as a supported manner to do things.
TL;DR: You should not be using blindly trusting keys for all repos (which is
what apt-key add does). See :
https://wiki.debian.org/DebianRepository/UseThirdParty
Something like this is the way it should be done:
# Install GPG Certcurl -fsSL
"https://www.postgresql.org/media/keys/ACCC4CF8.asc; \
| gpg --dearmor \
| sudo sponge /etc/apt/keyrings/postgresql.gpg
# Create source list file
. /etc/os-release
echo "deb [signed-by=/etc/apt/keyrings/postgresql.gpg]
https://apt.postgresql.org/pub/repos/apt ${VERSION_CODENAME}-pgdg main" \
| doas sponge /etc/apt/sources.list.d/postgresql.list
## Install
doas apt-get update && apt-get -y install postgresql
Re: array_to_json/array_agg question
> You almost got the subrecord ("value_1" and "value_2") right. You need
> to use json_build_object() (or even the new json_object() function added
> in pg16) instead of row_to_json() to just include "value_1" and
> "value_2". Then GROUP BY "key" and aggregate the subrecords with
> json_agg(). Then build the top-level record ("key" and "values") with
> json_build_object(). And finally one more aggregation with json_agg()
> to get a single array.
>
Interesting ideas, thanks Erik.
Subsequent to my original posting, but prior to your reply and based on an
off-list idea from someone else, I came up with the following adaptation:
SELECT json_agg(q) INTO v_res FROM (SELECT
array_to_json(array_agg(row_to_json(p))) AS q
FROM (SELECT * FROM test_a)p group by key)s;
But maybe I should be considering json_build_object() instead, or maybe
json_object() (although I'm currently on 14.5, so it would require an upgrade
to 16 first, which is possible as a longer-term option, but right now I'm
developing against a 14.5 backend).
array_to_json/array_agg question
Hi
Before I go down the road of taking care of this in the front-end through
iterations, I thought I would ask the pgsql if there was a clever query I could
do on postgres that would take care of it for me instead.
In essence, I would like to consolidate values from the same key as a json
array, so instead of :
[{"key":"one","value_1":"foo","value_2":"foo"},{"key":"one","value_1":"foo","value_2":"bar"}]
I would have (forgive me if my JSON syntax is incorrect here) :
[{"key":"one",[{"value_1":"foo","value_2":"foo"},{"value_1":"foo","value_2":"bar"}]}]
A simplified example of where I am at the moment:
create table test_a(key text,value_1 text,value_2 text);insert into
test_a(key,value_1,value_2) values('one','foo','foo');
insert into test_a(key,value_1,value_2) values('one','foo','bar');
insert into test_a(key,value_1,value_2) values('two','bar','foo');
select array_to_json(array_agg(row_to_json(p))) from (select * from test_a
where key='one') p;
[{"key":"one","value_1":"foo","value_2":"foo"},{"key":"one","value_1":"foo","value_2":"bar"}]
Re: Function inserting into tstzrange ? (syntax error at or near...)
> > There's not bespoke SQL syntax for constructing a range. You must > use a function, something like > > VALUES(p_event_id, tstzrange(p_start_time,p_end_time,'[)')) ... Thanks all for your swift replies. Serves me right for assuming I could use variable substitution where text would normally go, i.e. I thought I could just mimic the below example from the docs by substituting the variables: INSERT INTO reservation VALUES (1108, '[2010-01-01 14:30, 2010-01-01 15:30)'); Lesson learnt ! Thanks again.
Function inserting into tstzrange ? (syntax error at or near...)
I'm sure I'm doing something stupid here, but I think I've got the syntax right ? The error I'm seeing: psql:event_session_funcs.sql:26: ERROR: syntax error at or near "[" LINE 11: VALUES(p_event_id,[p_start_time,p_end_time)) RETURNI... The function: CREATE OR REPLACE FUNCTION new_event_session(p_event_id text, p_start_time timestamptz, p_end_time timestamptz, p_sess_title text, p_sess_desc text ) RETURNS text AS $$ DECLARE v_session_id text; BEGIN INSERT INTO event_sessions(event_id,evt_sess_times) VALUES(p_event_id,[p_start_time,p_end_time)) RETURNING evt_sess_id INTO v_session_id; // REST OF FUNCTION REMOVED FOR BREVITY The table definition: CREATE TABLE IF NOT EXISTS event_sessions ( event_id text NOT NULL, evt_sess_id text NOT NULL PRIMARY KEY DEFAULT ksuid_pgcrypto_micros(), evt_sess_times tstzrange NOT NULL, evt_sess_inserted timestamptz not null default now(), CONSTRAINT fk_evt_id FOREIGN KEY(event_id) REFERENCES events(event_id), EXCLUDE USING gist ( event_id WITH =, evt_sess_times WITH && ) ); N.B. I'm calling from Go, the library does not natively support tstzrange, hence the need to break-out the input parameters. Thanks !
Re: Scriptable way to validate a pg_dump restore ?
On Monday, 29 January 2024 at 09:06, Ron Johnson wrote: > > That's kinda like being asked to prove that rocks always fall when you drop > them. Either you trust physics, because physics has always worked, or you > must watch every rock, because next time it might not fall. The analogy is > slightly flawed, since we always check the pg_dump and pg_restore return > codes, since something else might impact their function. > > But if you still need evidence, here's what I'm doing to verify table and > record counts during a 9.6 -> 14 migration. You'll have to modify it for your > purpose. Thanks Ron ! I must admit that I am willing to trust pg_dump / pg_restore, mostly for the reasons Adrian Klaver implied. However your script is likely the very thing I was looking for in terms of belt & braces. So I appreciate you publishing it as a source of inspiration !
Scriptable way to validate a pg_dump restore ?
Hi Let's say I've got a scenario where I'm doing a pg_dump replication rather than online streaming, e.g. due to air-gap or whatever. Is there a scriptable way to validate the restore ? e.g. using doing something clever with ctid or something to ensure both the schema and all its rows were restored to the same point at which the dump was taken ? Thanks ! Laura
Re: Postgresql HA cluster
--- Original Message --- On Friday, October 13th, 2023 at 14:10, Jehan-Guillaume de Rorthais wrote: > But really, double check first why a simple primary-standby architecture > doesn't > meet your needs. The simpler the architecture is, the better. Even from the > application point of view. > >From my perspective I do wonder why Postgres doesn't have an equivalent to >MySQL Group Replication. Although you can run MySQL GR as multi-primary, most people run it as primary-standby. However the difference with Postgres is that MySQL Group does leader election. Whilst Postgres failover/failback is a highly manual affair.
Re: Maintaining accents with "COPY" ?
> Looks like an encoding issue and a mismatch between database encoding and > client > encoding. You can check both with: > > SHOW server_encoding; > SHOW client_encoding; > > Then either set the client encoding or use COPY's encoding option to match the > database encoding (I assume utf8 in this example): > > SET client_encoding = 'utf8'; > COPY (...) TO /tmp/bar.csv DELIMITER ',' CSV HEADER ENCODING 'utf8'; > > -- > Erik Hi Erik, Looks like you could well be right about encoding: postgres=# SHOW server_encoding; server_encoding - UTF8 (1 row) postgres=# SHOW client_encoding; client_encoding - SQL_ASCII (1 row) I will try your suggestion...
Maintaining accents with "COPY" ?
Hi I'm currently doing a CSV export using COPY: COPY (select * from foo where bar='foo') TO '/tmp/bar.csv' DELIMITER ',' CSV HEADER; This works great apart from accents are not preserved in the output, for example é gets converted to random characters, e.g. √© or similar. How can I preserve accents ? Thanks ! Laura
pg RLS suggestions needed
I've got a table with an RLS policy on it: Policies: POLICY "app_users_policy" USING ((app_id = CURRENT_USER)) Is there a way I'm not aware of (e.g. via Pl/PGSQL) that would allow "migration" of data from one RLS owner to another ? At the moment, the only option I can think of is for the external app to login as one user, cache the data and then login as the other user and re-save it. Basically the background is that I want to give users the ability to self-service migrate their existing app profile from "old" to "new" (the app being differentiated via RLS user on app_id)
Re: Fwd: Modelling a web CMS in Postgres ... a little advice needed
Sent with Proton Mail secure email. --- Original Message --- On Thursday, August 11th, 2022 at 13:11, Ray O'Donnell wrote: > On 11/08/2022 11:00, Laura Smith wrote: > > > Hi Tony > > > > The reason I'm looking to do it from scratch is that its a case of > > "once bitten, twice shy". > > > > This CMS will be replacing a Joomla based CMS. > > > > I can't quite say I'm enamoured by the option of trading one "baggage > > included" opinionated CMS for another. Also in this day and age, > > removing the excess baggage means an improved security footprint. > > > I would second what Tony Shelver said - it sounds as if what you're > trying to do has already been done, and for the most part done well. I'd > recommend Drupal, which works well with PostgreSQL, and which is very > extensible and customisable. > > I'd agree with you that removing baggage can mean improved security - > on the other hand, having a huge, security-conscious community behind an > open-source project gets you there too. > > Ray. > Tony's suggestion also needed Node.js, and frankly I'm not even going to open that can of worms. At this point I'm not interested in "very extensible and customisable". Joomla fills those words too, and my present installation is full of all sorts of third-party extensions ... the thought of all the security vulnerabilities scares the bejeezus out of me ! Hence I want to get rid of bells, whistles and baggage. Forget the fancy GUI, forget the "community" of random plugins. I just want a solid Postgres, which exposes to the outside world via a limited number of Pl/pgsql functions. The frontend can then pull the data and render it into a template. I know I probably sound nuts. But as I said, I've spent too long in the hell of Joomla. I don't want to replace it with another thing that tries to be all things to all people ... I just want to follow the KISS principle now.
Re: Fwd: Modelling a web CMS in Postgres ... a little advice needed
Hi Tony
The reason I'm looking to do it from scratch is that its a case of "once
bitten, twice shy".
This CMS will be replacing a Joomla based CMS.
I can't quite say I'm enamoured by the option of trading one "baggage included"
opinionated CMS for another. Also in this day and age, removing the excess
baggage means an improved security footprint.
Laura
--- Original Message ---
On Thursday, August 11th, 2022 at 10:49, Tony Shelver
wrote:
> From: Tony Shelver
> Date: Thu, 11 Aug 2022 at 11:47
> Subject: Re: Modelling a web CMS in Postgres ... a little advice needed
> To: Laura Smith
>
>
>
> On Thu, 11 Aug 2022 at 09:35, Laura Smith
> wrote:
>
> > Hi
> >
> > I'm looking at using pgsql as a backend to a web CMS but could do with a
> > little advice from the crowd on the wiseness of my schema thinking.
> >
> > TL;DR the design is centered around two tables "pages" and "page_content",
> > where "pages" has a jsonb column that refers to "page_content" in a
> > key-value style (key for content block location ID on the web page, value
> > for the database lookup). Probably both, but certainly "page_content" would
> > need to be versioned.
> >
> > My present thinking is along the following lines (table columns minimised
> > for this post):
> >
> > create table pages (
> > page_id uuid primary key not null,
> > page_metadata jsonb not null
> > );
> >
> > create table page_content(
> > content_id uuid not null,
> > content_version_id uuid not null
> > content_valid tstzrange not null default tstzrange(now(),'infinity'),
> > content_data text,
> > EXCLUDE USING gist (content_id WITH =, content_valid WITH && ) DEFERRABLE
> > INITIALLY DEFERRED
> > );
> > create unique index if not exists on page_content(content_version_id);
> > CREATE OR REPLACE VIEW current_content AS select * from page_content where
> > content_valid @> now();
> >
> >
> > An example "page_metadata" entry might look something like :
> > {
> > "page":"foo",
> > "description":"bar",
> > "content":[
> > "pageHeader":"E52DD77C-F3B5-40D9-8E65-B95F54E1C76B",
> > "pageMainLeft":"0BEFA002-7F9B-4A6A-AD33-CA916751B648"
> > ]
> > }
> >
> >
> > So I guess my questions here are :
> > Am i nuts with this thinking ? Is there a saner way to do this ? Should I
> > be using pgsql at all for this, e.g. the cool kids will probably say I
> > should be using a graph database ? (N.B. I did consider a pure old-school
> > relational model with no jsonb, but I considered it too difficult to model
> > the dynamic nature of the fields, i.e. unknown many-many relationship
> > between page content locations and content ... but I'm willing to be proven
> > wrong by wiser minds)
> >
> > Then, on a more technical level what would an optimal query for
> > looping through the json content array look like ? I have always been
> > pretty useless when it comes to CTE expressions !
> >
> > Thanks all
> >
>
> Off the top of my head, it seems like you are reinventing the wheel.
>
> There are some very good CMS systems out there, such as Strapi, which is an
> open source, headless CMS, and which can use Postgresql as the underlying DB.
>
> It could be worth a while to install this and see how it generates the
> underlying pages. As a benefit, it also generates the UI to maintain the
> underlying data, and also provides a set of APIs that you can call to access
> the data from many different front-ends.
>
> Tony
Modelling a web CMS in Postgres ... a little advice needed
Hi
I'm looking at using pgsql as a backend to a web CMS but could do with a little
advice from the crowd on the wiseness of my schema thinking.
TL;DR the design is centered around two tables "pages" and "page_content",
where "pages" has a jsonb column that refers to "page_content" in a key-value
style (key for content block location ID on the web page, value for the
database lookup). Probably both, but certainly "page_content" would need to be
versioned.
My present thinking is along the following lines (table columns minimised for
this post):
create table pages (
page_id uuid primary key not null,
page_metadata jsonb not null
);
create table page_content(
content_id uuid not null,
content_version_id uuid not null
content_valid tstzrange not null default tstzrange(now(),'infinity'),
content_data text,
EXCLUDE USING gist (content_id WITH =, content_valid WITH && ) DEFERRABLE
INITIALLY DEFERRED
);
create unique index if not exists on page_content(content_version_id);
CREATE OR REPLACE VIEW current_content AS select * from page_content where
content_valid @> now();
An example "page_metadata" entry might look something like :
{
"page":"foo",
"description":"bar",
"content":[
"pageHeader":"E52DD77C-F3B5-40D9-8E65-B95F54E1C76B",
"pageMainLeft":"0BEFA002-7F9B-4A6A-AD33-CA916751B648"
]
}
So I guess my questions here are :
Am i nuts with this thinking ? Is there a saner way to do this ? Should I be
using pgsql at all for this, e.g. the cool kids will probably say I should be
using a graph database ? (N.B. I did consider a pure old-school relational
model with no jsonb, but I considered it too difficult to model the dynamic
nature of the fields, i.e. unknown many-many relationship between page content
locations and content ... but I'm willing to be proven wrong by wiser minds)
Then, on a more technical level what would an optimal query for looping
through the json content array look like ? I have always been pretty useless
when it comes to CTE expressions !
Thanks all
Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)
On Wednesday, April 6th, 2022 at 17:41, W.P. wrote: > That was not a "command". PG was working ok, DB on encrypted (LUKS on > LVM2, ext4) USB drive. > I mean, why ? If there was ever a "setting yourself up to fail" scenario, it would be this. Just because you can do it, doesn't mean you should. I suspect you will have to file this under "learnt the hard way"
Re: Open SSL Version Query
Hi Sahaj AFAIK this is a question for you to ask your chosen OS provider. Postgres will be compiled against the system library (dynamic linking) therefore whether your version of OpenSSL has been patched against the vulnerability is a question for your OS provider, not Postgres. Unless of course you've installed your own version of OpenSSL, in which case you need to ask yourself. ;-) Laura --- Original Message --- On Wednesday, March 23rd, 2022 at 11:48, Sahaj Diwan wrote: > Hi Team, > > Do we use openssl version 1.0.2, 1.1.1 or 3.0 in postgre 13.If yes then these > version are vulnerable CVE-2022-0778 which is now fixed in later ssl version. > > Regards,Sahaj
Re: UUID type question
Sent with ProtonMail Secure Email. --- Original Message --- On Friday, February 18th, 2022 at 14:25, Dominique Devienne wrote: > > You might also be interested in KSUID, e.g. https://github.com/segmentio/ksuid I love KSUID. I use it in all new projects. ;-)
UUID type question
I'm *sure* I've seen this discussed on here before - infact I'm worried it might even have been me who asked the question ! But I've searched the archives without luck, so here goes : Is there anything inherently "special" about the UUID type ? i.e. if I store a UUID in a text is it "worse" ? (I'm speaking here about performance & storage, obviously, not the fact that uuid enforces the formatting). Thanks ! Laura
Re: pg_basebackup with hostssl ?
Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Wednesday, February 2nd, 2022 at 17:20, Adrian Klaver wrote: > > Before you do that I would establish that you are connecting to the > > correct Postgres instance. > > Good news, all up and running ! The new "postgresql.auto.conf" automagic is pure awesome. ;-)
Re: pg_basebackup with hostssl ?
Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Wednesday, February 2nd, 2022 at 16:50, Adrian Klaver wrote: > Not completely: > > https://www.postgresql.org/docs/current/app-pgbasebackup.html > > -d connstr > > --dbname=connstr > > Specifies parameters used to connect to the server, as a > > ; these will override any conflicting command line > > options. > > The option is called --dbname for consistency with other client > > applications, but because pg_basebackup doesn't connect to any > > particular database in the cluster, any database name in the connection > > string will be ignored. > Seems like I was looking for the wrong words on the right page ! Thanks, will try that.
Re: pg_basebackup with hostssl ?
Forgot to add that I also have : "hostsslallall10.0.0.0/8md5"
Re: pg_basebackup with hostssl ?
Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Wednesday, February 2nd, 2022 at 16:30, Adrian Klaver wrote: > I am not following. pg_basebackup is a stand alone client that is not > > involved in replication other then establishing a starting point from > > which to later establish a replication relationship. > Indeed. And that is exactly that I am trying to do (i.e. have master, want new slave). > The error is saying that whatever instance you are pointing > > pg_basebackup at does not have entry in pg_hba.conf for the > > host/user/encryption combination pg_basebackup is trying to connect as. > But it *DOES* have an entry, per my original message: "hostsslreplicationall10.0.0.0/8md5" And yes, that hba is loaded and live because other remote clients are happily connected to that server and thus reliant on a valid hba.conf. The host is correct. The user is correct. The point I am making is that pg_basebackup is connecting without encryption and I want to know how to tell it to use encryption. The docs are silent on the subject.
pg_basebackup with hostssl ?
I've had a quick glance through the man page for pg_basebackup but can't see any flags to set ssl. Long story short, I've got the following in pg_hba.conf of the master: hostsslreplicationall10.0.0.0/8md5 But the slave is complaining: FATAL: no pg_hba.conf entry for replication connection from host "10.1.2.3", user "myrepl", no encryption I suspect this is almost certainly because I'm using "hostssl" instead of "host". But other than the obvious and undesirable quick-fix, how should I be calling pg_basebackup to make sure it uses encryption ?
Re: Counting the number of repeated phrases in a column
‐‐‐ Original Message ‐‐‐ On Tuesday, January 25th, 2022 at 17:10, Shaozhong SHI wrote: > 'The City of London, London' also has no occurrences of any repeated phrase. Not sure the City would be particularly happy with that attribution. ;-) Its it sits on its own. Its own local authority, its own county. It is an enclave enclosed by Greater London. A bit like the Vatican really. Except the City isn't its own country - much to the chagrin of some, no doubt !
Re: ZFS filesystem - supported ?
Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Tuesday, October 26th, 2021 at 01:18, Benedict Holland wrote: > In my opinion, ext4 will solve any and all problems without a very deep > understanding of file system architecture. In short, i would stick with ext4 > unless you have a good reason not to. Maybe there is one. I have done this a > long time and never thought twice about which file system should support my > servers. > Curious, when it comes to "traditional" filesystems, why ext4 and not xfs ? AFAIK the legacy issues associated with xfs are long gone ?
Re: ZFS filesystem - supported ?
‐‐‐ Original Message ‐‐‐ On Saturday, October 23rd, 2021 at 18:48, Mladen Gogala wrote: > On 10/23/21 09:37, Laura Smith wrote: > > > Hi Mladen, > > > > Yes indeed, snapshots is the primary reason, closely followed by > > zfssend/receive. > > > > I'm no stranger to using LVM snapshots with ext4/xfs but it requires a > > custom shell script to manage the whole process around backups. I feel the > > whole thing could well be a lot cleaner with zfs. > > > > Thank you for the links, I will take a look. > > > > Laura > > Yes, ZFS is extremely convenient. It's a volume manager and a file > > system, all rolled into one, with some additiional convenient tools. > > However, performance is a major concern. If your application is OLTP, > > ZFS might be a tad too slow for your performance requirements. On the > > other hand, snapshots can save you a lot of time with backups, > > especially if you have some commercial backup capable of multiple > > readers. If your application is OLTP, ZFS might be a tad too slow for > > your performance requirements. The only way to find out is to test. The > > ideal tool for testing is pgio: > > https://kevinclosson.net/2019/09/21/announcing-pgio-the-slob-method-for-postgresql-is-released-under-apache-2-0-and-available-at-github/ > > For those who do not know, Kevin Closson was the technical architect who > > has built both Exadata and EMC XTRemIO. He is now the principal engineer > > of the Amazon RDS. This part is intended only for those who would tell > > him that "Oracle has it is not good enough" if he ever decided to post here. > > Thank you Mladen for your very useful food for thought. I think my plan going forward will be to stick to the old XFS+LVM setup and (maybe) when I have some more time on my hands fire up a secondary instance with ZFS and do some experimentation with pgio. Thanks again !
Re: ZFS filesystem - supported ?
On Saturday, October 23rd, 2021 at 14:03, Mladen Gogala wrote: > On 10/23/21 07:29, Laura Smith wrote: > > > Hi, > > > > Given an upcoming server upgrade, I'm contemplating moving away from XFS to > > ZFS (specifically the ZoL flavour via Debian 11). BTRFS seems to be falling > > away (e.g. with Redhat deprecating it etc.), hence my preference for ZFS. > > > > However, somewhere in the back of my mind I seem to have a recollection of > > reading about what could be described as a "strong encouragement" to stick > > with more traditional options such as ext4 or xfs. > > > > A brief search of the docs for "xfs" didn't come up with anything, hence > > the question here. > > > > Thanks ! > > > > Laura > > Hi Laura, > > May I ask why would you like to change file systems? Probably because of > > the snapshot capability? However, ZFS performance leaves much to be > > desired. Please see the following article: > > https://www.phoronix.com/scan.php?page=article=ubuntu1910-ext4-zfs=1 > > This is relatively new, from 2019. On the page 3 there are tests with > > SQLite, Cassandra and RocksDB. Ext4 is much faster in all of them. > > Finally, there is another article about relational databases and ZFS: > > https://blog.docbert.org/oracle-on-zfs/ > > In other words, I would test very thoroughly because your performance is > > likely to suffer. As for the supported part, that's not a problem. > > Postgres supports all modern file systems. It uses Posix system calls to > > manipulate, read and write files. Furthermore, if you need snapshots, > > disk arrays like NetApp, Hitachi or EMC can always provide that. > > Regards > > > -- > > Mladen Gogala > > Database Consultant > > Tel: (347) 321-1217 > > https://dbwhisperer.wordpress.com Hi Mladen, Yes indeed, snapshots is the primary reason, closely followed by zfssend/receive. I'm no stranger to using LVM snapshots with ext4/xfs but it requires a custom shell script to manage the whole process around backups. I feel the whole thing could well be a lot cleaner with zfs. Thank you for the links, I will take a look. Laura
ZFS filesystem - supported ?
Hi, Given an upcoming server upgrade, I'm contemplating moving away from XFS to ZFS (specifically the ZoL flavour via Debian 11). BTRFS seems to be falling away (e.g. with Redhat deprecating it etc.), hence my preference for ZFS. However, somewhere in the back of my mind I seem to have a recollection of reading about what could be described as a "strong encouragement" to stick with more traditional options such as ext4 or xfs. A brief search of the docs for "xfs" didn't come up with anything, hence the question here. Thanks ! Laura
Debian APT repo instructions need updating
Not sure if this is the right place to post it, but I wanted to higlight that the Debian repo instructions (https://www.postgresql.org/download/linux/debian/) need updating to bring them inline with Debian best practices. As per https://wiki.debian.org/DebianRepository/UseThirdParty: "The key MUST be downloaded over a secure mechanism like HTTPS to a location only writable by root, which SHOULD be /usr/share/keyrings. The key MUST NOT be placed in /etc/apt/trusted.gpg.d or loaded by apt-key add. A sources.list entry SHOULD have the signed-by option set. The signed-by entry MUST point to a file, and not a fingerprint." This is actually doubly important because bullseye is the final Debian release to ship apt-key.
RE: Multi-master replication
‐‐‐ Original Message ‐‐‐ On Friday, August 13th, 2021 at 12:35 PM, Zahir Lalani wrote: > Confidential > > Thx Laura > > So here is the dilemma - everything in the cloud world tends toward > horizontal scaling. We do that with PG using single master and multiple > slaves. But we are write heavy and of course the load on the master is quite > high. It does seem that multi-master has risks (everyone seems to talk about > conflict resolution!) so I wonder whether the sensible route is simply to > scale vertically on the master? > Scaling on the master remains a popular choice these days even in the trendy world of cloud and horizontal scaling. See, for example Let's Encrypt - admittedly a MySQL/InnoDB infrastructure, but still master/replica - https://letsencrypt.org/2021/01/21/next-gen-database-servers.html I suppose (depending on the nature of your data), you could perhaps use sharding, so multiple master/slaves with data (and hence load) spread across. Most likely you would need something like Redis at the frontend to push clients in to the right shard. Conflict resolution (whether due to split brain or otherwise) is a big worry of mine with multi-master setups. The Googles and Facebook's of this world seem to be solving the problem on the basis of having super precise time sync setup on their database servers and use high-resolution timestamps to act as tie-break arbitrator. But of course that comes with significant expense as it needs PTP infrastructure.
Re: Multi-master replication
‐‐‐ Original Message ‐‐‐ On Friday, August 13th, 2021 at 9:00 AM, Zahir Lalani wrote: > Confidential > > Hello all > > Does anyone on the list have production experience of using PG in a > multi-master setup – if so using which tools? > > Thx > > Zahir I have sometimes considered it, but it always struck me that given there is no first-class support for it, multi-master in postgres is just an opportunity for so many foot-guns to enable to things to go terribly, terribly wrong. Maybe you need to look at something like CockroachDB which has a degree of Postgres syntax compatability (https://www.cockroachlabs.com/blog/why-postgres/). N.B. Not speaking from experience here, CockroachDB is something I've wanted to play with for a long time but never had the time !
PostgreSQL Apt Repository instructions need updating
Re: https://www.postgresql.org/download/linux/debian/ The instructions presented for key handling are not inline with Debian best-practices As per https://wiki.debian.org/DebianRepository/UseThirdParty: "The key MUST be downloaded over a secure mechanism like HTTPS to a location only writable by root, which SHOULD be /usr/share/keyrings. The key MUST NOT be placed in /etc/apt/trusted.gpg.d or loaded by apt-key add. A sources.list entry SHOULD have the signed-by option set. The signed-by entry MUST point to a file, and not a fingerprint."
Re: returning setof from insert ?
‐‐‐ Original Message ‐‐‐ On Wednesday, July 14th, 2021 at 1:14 PM, Thomas Kellerer wrote: > Laura Smith schrieb am 14.07.2021 um 13:22: > > > A bit of pl/pgsql writer's block going on here ... > > > > Postgres complains "RETURN cannot have a parameter in function returning > > set" in relation to the below. I don't really want to have to "RETURNS > > TABLE" because that means I have to enumerate all the table columns. > > > > I'm sure I'm missing something simple here ! > > > > CREATE OR REPLACE FUNCTION foobar(foo text,bar text) RETURNS SETOF bar AS $$ > > > > DECLARE > > > > v_row bar%ROWTYPE; > > > > BEGIN > > > > insert into bar(f,b) values(foo,bar) returning * into v_row; > > > > return v_row; > > > > END; > > > > $$ language plpgsql; > > You need to use RETURN NEXT: > > CREATE OR REPLACE FUNCTION foobar(foo text,bar text) > > RETURNS SETOF bar > > AS $$ > > DECLARE > > v_row bar%ROWTYPE; > > BEGIN > > insert into bar(f,b) values(foo,bar) returning * into v_row; > > return next v_row; > > END; > > $$ > > language plpgsql; > > But you don't need PL/pgSQL for this or store the result in a variable: > > CREATE OR REPLACE FUNCTION foobar(foo text,bar text) > > RETURNS SETOF bar > > AS $$ > > insert into bar(f,b) values(foo,bar) > > returning *; > > $$ > > language sql; Thank you for the tip on making it cleaner with sql. As you say, no real need for PL/pgSQL in this instance, but I'll try to remember RETURN NEXT in the future !
returning setof from insert ?
Hi, A bit of pl/pgsql writer's block going on here ... Postgres complains "RETURN cannot have a parameter in function returning set" in relation to the below. I don't really want to have to "RETURNS TABLE" because that means I have to enumerate all the table columns. I'm sure I'm missing something simple here ! CREATE OR REPLACE FUNCTION foobar(foo text,bar text) RETURNS SETOF bar AS $$ DECLARE v_row bar%ROWTYPE; BEGIN insert into bar(f,b) values(foo,bar) returning * into v_row; return v_row; END; $$ language plpgsql;
Re: Ideas for building a system that parses medical research publications/articles
Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Saturday, 5 June 2021 12:14, Achilleas Mantzios wrote: > > I know its a huge work, but you are missing a point. Nobody wishes to > compete with anyone. This is a about a project, a parent-advocacy > non-profit that ONLY aims to save the sick children (or maybe also > very young adults) of a certain spectrum . So the goal is to make the > right tools for researchers, clinicians and parents. This market is too > small to even consider making any money out of it, but the research is > still very expensive and the progress slower than optimum. Unfortunately I'm not "missing a point", your final paragraph summarises your position. You have been taken in by the very charitable goal of saving sick children. Unfortunately your head has been disconnected from your heart. If we put the charitable purpose to one side and take a purely objective view at what you want to do, my original statement still stands, i.e. the certainty that you are grossly underestimating the technical and practical complexities of what you want to achieve.
Re: Ideas for building a system that parses medical research publications/articles
‐‐‐ Original Message ‐‐‐ On Saturday, 5 June 2021 10:49, Achilleas Mantzios wrote: > Hello > > I am imagining a system that can parse papers from various sources > (web/files/etc) and in various formats (text, pdf, etc) and can store > metadata for this paper ,some kind of global ID if applicable, authors, > areas of research, whether the paper is "new", "highlighted", > "historical", type (e.g. Case reports, Clinical trials), symptoms (e.g. > tics, GI pain, psychological changes, anxiety, ), and other key > attributes (I guess dynamic), it must be full text searchable, etc. > > I am at the very beginning in this and it is done on a fully volunteer > basis. > > Lots of questions : is there any scientific/scholar analysis software > already available? If yes and is really good and open source , then this > will influence the rest of decisions. Otherwise , I'll have to form a > team that can write one, in this case I'll have to decide DB, language, > etc. I work 20 years with pgsql so it is the natural choice for any kind > of data, I just ask this for the sake of completeness. > > All ideas welcome. Hello Achilleas Not wishing to be discouraging, but you have very ambitious goals for what sounds like a one-person project ? You are effectively looking at competing with platforms such as Elsevier Scopus/Scival which are market-leaders in the area for good reason (i.e. it takes a lot of manpower to write algorithms, manage metadata etc., and the only way to consistently maintain that manpower is to employ people, lots of them). There are also things like Google Scholar around the place. I think before starting on the technical side of Postgres etc., the honest truth is that you need to do more planning, both in terms of implementation and long-term sustainability. For example, before we even get to metadata, you talk of various sources and formats. Have you considered licensing issues ? Have you considered how to keep the dataset clean ? (If you are thinking you can just scrape the web, then you'll be in for a surprise). Laura
Re: Struggling with EXCLUDE USING gist
Sent with ProtonMail Secure Email.
‐‐‐ Original Message ‐‐‐
On Friday, 4 June 2021 18:45, Mark Dilger wrote:
> > On Jun 4, 2021, at 9:47 AM, Laura Smith n5d9xq3ti233xiyif...@protonmail.ch
> > wrote:
> > CREATE TABLE test (
> > t_val text not null,
> > t_version text unique not null default gen_random_uuid() ,
> > t_range tstzrange not null default tstzrange('-infinity','infinity'),
> > EXCLUDE USING gist (t_val WITH=, t_range WITH &&) DEFERRABLE INITIALLY
> > DEFERRED
> > );
>
>
>
> > INSERT INTO test(t_val) values(p_val);
>
> This will insert a t_range of ('-infinity','infinity'), won't it? Wouldn't
> you want to instead insert with t_range starting around now() rather than
> starting at -infinity?
>
> —
> Mark Dilger
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
That seems to have done the trick. Thanks again Mark,
Re: Struggling with EXCLUDE USING gist
Sent with ProtonMail Secure Email.
‐‐‐ Original Message ‐‐‐
On Friday, 4 June 2021 18:45, Mark Dilger wrote:
> > On Jun 4, 2021, at 9:47 AM, Laura Smith n5d9xq3ti233xiyif...@protonmail.ch
> > wrote:
> > CREATE TABLE test (
> > t_val text not null,
> > t_version text unique not null default gen_random_uuid() ,
> > t_range tstzrange not null default tstzrange('-infinity','infinity'),
> > EXCLUDE USING gist (t_val WITH=, t_range WITH &&) DEFERRABLE INITIALLY
> > DEFERRED
> > );
>
>
>
> > INSERT INTO test(t_val) values(p_val);
>
> This will insert a t_range of ('-infinity','infinity'), won't it? Wouldn't
> you want to instead insert with t_range starting around now() rather than
> starting at -infinity?
>
> —
> Mark Dilger
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
Interesting. I will go test. It hadn't occurred to me the start time might be
what was causing all the errors.
Thanks for the suggestion Mark. I will report back.
Re: Struggling with EXCLUDE USING gist
Sent with ProtonMail Secure Email.
‐‐‐ Original Message ‐‐‐
On Friday, 4 June 2021 18:11, Julien Rouhaud wrote:
> On Sat, Jun 5, 2021 at 12:48 AM Laura Smith
> n5d9xq3ti233xiyif...@protonmail.ch wrote:
>
> > All the examples I've seen around the internet make this sound so easy.
> > But I seem to be missing some important step because all I'm getting are
> > messages such as "DETAIL: Key (t_val, t_version)=(def,
> > [-infinity,infinity)) conflicts with existing key (t_val, t_version)=(def,
> > [-infinity,"2021-06-04 16:56:08.008122+01"))."
> > [...]
> > CREATE FUNCTION create_or_update_test(p_val text) RETURNS bool AS $$
> > DECLARE
> > v_version text;
> > v_range tstzrange;
> > BEGIN
> > -- N.B. Have coded it this way round (not insert first) because "ON
> > CONFLICT does not support deferrable unique constraints/exclusion
> > constraints as arbiters"
> > SELECT t_version,t_range into v_version,v_range from test_v where
> > t_val='abc';
> > IF NOT FOUND THEN
> > INSERT INTO test(t_val) values(p_val)
> > END IF;
> > -- If range conflict, adjust old and set new
> > UPDATE test set t_range=tstzrange(lower(v_range),now(),'[)') where
> > t_version=v_version;
> > INSERT INTO test(t_val) values(p_val);
> > RETURN FOUND;
> > END;
> > $$ language plpgsql;
>
> You need to provide more information. I suspect that what's happening
> is a concurrency issue where the create_or_update_test() is called
> multiple time and both initially see and empty table so try to insert
> an -infinity/infinity range before updating it, so the 2nd call will
> fail once the 1st one commits.
Happy to provide more information although not quite sure how much more I can
provide ? Perhaps my use case ?
My use-case is version tracking for items.
My implementation concept :
Default insert is tstzrange('-infinity','infinity')
When a "new" version of the item comes along:
(a) the "old" item becomes archived (i.e. valid until 'infinity' => valid until
'now()' )
(b) the "new" item becomes current (i.e. valid until 'infinity')
If tstzrange and EXCLUDE USING is the wrong way to do this sort of thing, then
I'm all ears to other suggestions. But I've seen so many examples out on the
web that suggest this is exactly the sort of thing that tstzrange and EXCLUDE
using *is* very good for ?
Re: Struggling with EXCLUDE USING gist
‐‐‐ Original Message ‐‐‐ On Friday, 4 June 2021 18:07, Adrian Klaver wrote: > On 6/4/21 9:47 AM, Laura Smith wrote: > > > All the examples I've seen around the internet make this sound so easy. > > But I seem to be missing some important step because all I'm getting are > > messages such as "DETAIL: Key (t_val, t_version)=(def, > > [-infinity,infinity)) conflicts with existing key (t_val, t_version)=(def, > > [-infinity,"2021-06-04 16:56:08.008122+01"))." > > That would be correct: > > select '[-infinity,infinity)'::tstzrange && '[-infinity,"2021-06-04 > 16:56:08.008122+01")'::tstzrange; > ?column? > > - > > t > > The ranges overlap so they fail the exclusion constraint. > So it seems we are agreed (me via error message, you via example) that a transaction (function script) that updates the "old" row to fixed timestamp before inserting a "new" row will not have the desired result. What is the solution then ? I need to keep historical versions but at the same time I need a "current" version. If I am not able to use "infinity" as bounds for "current" version then clearly I'm wasting my time trying to use EXCLUDE AS for version tracking because clearly using fixed timestamps instead of "infinity" for tstzrange would be a hacky fix that will be fragile and prone to breakage.
Struggling with EXCLUDE USING gist
All the examples I've seen around the internet make this sound so easy.
But I seem to be missing some important step because all I'm getting are
messages such as "DETAIL: Key (t_val, t_version)=(def, [-infinity,infinity))
conflicts with existing key (t_val, t_version)=(def, [-infinity,"2021-06-04
16:56:08.008122+01"))."
I'm on PostgresSQL 12.5 if it makes any difference.
It is my understanding that:
(a) Postgres functions are one big transaction and so what I'm trying to do in
my function code should work (i.e. update tstzrange before updating something
that would normally conflict).
(b) That infinity takes precedence over a defined point in time. The error I'm
receiving - shown above - seems to counter that perception though ?
Simplified example:
CREATE TABLE test (
t_val text not null,
t_version text unique not null default gen_random_uuid() ,
t_range tstzrange not null default tstzrange('-infinity','infinity'),
EXCLUDE USING gist (t_val WITH=, t_range WITH &&) DEFERRABLE INITIALLY DEFERRED
);
CREATE VIEW test_v AS select * from test where t_range @> now();
INSERT INTO test(t_val) values('abc');
CREATE FUNCTION create_or_update_test(p_val text) RETURNS bool AS $$
DECLARE
v_version text;
v_range tstzrange;
BEGIN
-- N.B. Have coded it this way round (not insert first) because "ON CONFLICT
does not support deferrable unique constraints/exclusion constraints as
arbiters"
SELECT t_version,t_range into v_version,v_range from test_v where t_val='abc';
IF NOT FOUND THEN
INSERT INTO test(t_val) values(p_val)
END IF;
-- If range conflict, adjust old and set new
UPDATE test set t_range=tstzrange(lower(v_range),now(),'[)') where
t_version=v_version;
INSERT INTO test(t_val) values(p_val);
RETURN FOUND;
END;
$$ language plpgsql;
Re: EXCLUDE USING and tstzrange
Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Friday, 4 June 2021 16:20, Joe Conway wrote: > On 6/4/21 10:58 AM, Laura Smith wrote: > > > One other question, what's the syntax for manipulating only the upper > > bound of a range. > > Say I have a Postgres function that does a "SELECT INTO" for an > > existing tsrange. Is there an easy way to change the variable's > > upper bound whilst leaving the "old" lower bound intact ? > > There may be easier/better ways, but for example this works: > > 8<-- > insert into test > values(42, '[2021-01-01, 2021-06-03)'); > INSERT 0 1 > > select test_range from test where test_id = '42'; > test_range > > --- > > ["2021-01-01 00:00:00-05","2021-06-03 00:00:00-04") > (1 row) > > update test > set test_range = tstzrange(lower(test_range), > '2021-06-04', '[)') > where test_id = '42'; > UPDATE 1 > > select test_range from test where test_id = '42'; > test_range > > > > ["2021-01-01 00:00:00-05","2021-06-04 00:00:00-04") > (1 row) > 8<-- > > HTH, > > Joe > > > > Crunchy Data - http://crunchydata.com > PostgreSQL Support for Secure Enterprises > Consulting, Training, & Open Source Development Thanks Joe !
Re: EXCLUDE USING and tstzrange
Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Friday, 4 June 2021 15:44, Adrian Klaver wrote: > On 6/4/21 7:32 AM, Laura Smith wrote: > > > Hi, > > I'm having difficulty finding the right part of the docs for this one. > > Could someone kindly clarify: > > create table test ( > > test_id text, > > test_range tstzrange); > > Will "EXCLUDE USING gist (test_id WITH =, test_range WITH && )" work as > > expected or do I need to use "EXCLUDE USING gist (test_id WITH =, > > test_range WITH TIME ZONE && )" to ensure the timezone is correctly taken > > into account during comparison ? > > tstzrange is over timestamp with time zone, so time zones are already > taken into account. > > > Thanks ! > > Laura > > -- > > Adrian Klaver > adrian.kla...@aklaver.com Thank you Adrian ! One other question, what's the syntax for manipulating only the upper bound of a range. Say I have a Postgres function that does a "SELECT INTO" for an existing tsrange. Is there an easy way to change the variable's upper bound whilst leaving the "old" lower bound intact ?
EXCLUDE USING and tstzrange
Hi, I'm having difficulty finding the right part of the docs for this one. Could someone kindly clarify: create table test ( test_id text, test_range tstzrange); Will "EXCLUDE USING gist (test_id WITH =, test_range WITH && )" work as expected or do I need to use "EXCLUDE USING gist (test_id WITH =, test_range WITH TIME ZONE && )" to ensure the timezone is correctly taken into account during comparison ? Thanks ! Laura
Re: Internationalisation (i18n) with Postgres as backend
On Wednesday, 2 June 2021 00:42, Rob Sargent wrote: > On 6/1/21 5:00 PM, Laura Smith wrote: > > > > What is your notion of "object". I first assumed it was akin to > > > "document" but then pages have objects. > > > > I think my terminology is a bit off. > > > > A document/page has object(s) on it. > > > > Or, perhaps better expressed, think of document/page as the template and > > object(s) is what fills the gaps in the template. > > Then I take it this template (layout?) is to be re-used across disparate > content. So /instances/ of template applied to content (of vice versa?) are > documents. Two separate domains to be managed, no? Is this an authoring > system? Hard copy or digital presentation? Yes, you are thinking along the right lines. The templates themselves are out-of-scope for the database, they are defined and managed in front-end as local filesystem files for the relevant parser to render since frontend tech is generally better suited to this role than databases. The role of the database is (when given a document ID) : - Tell the frontend which template to use - Provide content to fill template placeholders The content may take two forms: (a) Pure "fill the gaps" content for template placeholders (i.e. text for here, image for there etc.) (b) A list result set which then gets rendered by frontend (i.e think change lists, news items, lists of people's bios etc.) Versioning is a requirement of the system, particularly at content level. The internationalisation requirement comes in at content level. The templates themselves don't have any translatable elements. So, I guess TL;DR is yes, an authoring system. Currently only spec'd for digital (browser and mobile access), no print. Hope this helps clarify !
Re: Internationalisation (i18n) with Postgres as backend
Hi Steve,
I didn't consider hstore, I did consider jsonb though.
The thing that made me lean towards individual rows rather than consolidated
was that I thought versioning would ultimately be easier/cleaner to achieve
with individual rows (e.g. using tsrange & gist exclude). But willing to be
proven wrong.
Laura
‐‐‐ Original Message ‐‐‐
On Tuesday, 1 June 2021 22:10, Steve Baldwin wrote:
> Hi Laura,
>
> Did you consider using hstore to store language and data as a kvp? For
> example:
>
> b2bc_owner@b2bcreditonline=# create table langtest(pageid text, objectid
> text, objectdata hstore, constraint langtest_pk primary key (pageid,
> objectid));
> CREATE TABLE
> b2bc_owner@b2bcreditonline=# insert into langtest values ('zz', 'abc',
> '"en"=>"en for abc","de"=>"de for abc"');
> INSERT 0 1
> b2bc_owner@b2bcreditonline=# insert into langtest values ('zz', 'def',
> '"en"=>"en for def"');
> INSERT 0 1
> b2bc_owner@b2bcreditonline=# create or replace function langtestfunc(text,
> text, text[]) returns text language sql as $$ select a.data from langtest as
> t, unnest(t.objectdata->$3) as a(data) where t.pageid = $1 and t.objectid =
> $2 and a.data is not null limit 1 $$;
> b2bc_owner@b2bcreditonline=# select langtestfunc('zz', 'abc', array['de',
> 'en']);
> langtestfunc
> --
> de for abc
> (1 row)
> b2bc_owner@b2bcreditonline=# select langtestfunc('zz', 'def', array['de',
> 'en']);
> langtestfunc
> --
> en for def
> (1 row)
>
> Just a thought.
>
> Cheers,
>
> Steve
>
> On Wed, Jun 2, 2021 at 6:09 AM Laura Smith
> wrote:
>
> > Hi,
> >
> > I'm creating a Postgres backend for an internal tool which is essentially a
> > very simple implementation of multi-lingual CMS.
> >
> > So far my thoughts are along the lines of the below, but I would appreciate
> > a second (or more !) pair of eyes from some Postgresql gurus. I am
> > especially interested in feedback and suggestions in relation to the
> > following questions:
> >
> > (a) Is this going to work as expected (i.e. have I missed some obvious
> > foot-guns ?)
> >
> > (b) Is this manner of doing things reasonably efficient or are there better
> > ways I should be thinking of ? (bear in mind the schema is not set in
> > stone, so completely out of the box suggestions welcome !).
> >
> > The basic design concept (oversimplified) is: For each page, you have one
> > or more objects and those objects may have content in one or more languages.
> >
> > create table langtest(
> > pageid text not null,
> > objectid text not null ,
> > objectlang text not null,
> > objectdata text not null);
> >
> > create unique index on (pageid,objectid,objectlang);
> >
> > insert into langTest(pageID,objectID,objectLang,objectData) values
> > ('zzz','abc','en','Lorem ipsum dolor sit amet');
> > insert into langTest(pageID,objectID,objectLang,objectData) values
> > ('zzz','abc','de','Amet sit dolor ipsum lorem');
> > insert into langTest(pageID,objectID,objectLang,objectData) values
> > ('zzz','def','en','Dolor ipsum amet sit lorem');
> >
> > select distinct on(objectid)objectid,objectlang,pageid,objectdata from
> > langTest where pageid='zzz' and objectLang = any('{de,en}'::text[]) order
> > by objectid,array_position('{de,en}'::text[],objectLang);
> >
> > (The idea being that the select query will be wrapped into a function which
> > the frontend will call, passing a list of elegible languages as input)
> >
> > Thanks !
> >
> > Laura
Re: Internationalisation (i18n) with Postgres as backend
> What is your notion of "object". I first assumed it was akin to > "document" but then pages have objects. I think my terminology is a bit off. A document/page has object(s) on it. Or, perhaps better expressed, think of document/page as the template and object(s) is what fills the gaps in the template.
Internationalisation (i18n) with Postgres as backend
Hi,
I'm creating a Postgres backend for an internal tool which is essentially a
very simple implementation of multi-lingual CMS.
So far my thoughts are along the lines of the below, but I would appreciate a
second (or more !) pair of eyes from some Postgresql gurus. I am especially
interested in feedback and suggestions in relation to the following questions:
(a) Is this going to work as expected (i.e. have I missed some obvious
foot-guns ?)
(b) Is this manner of doing things reasonably efficient or are there better
ways I should be thinking of ? (bear in mind the schema is not set in stone, so
completely out of the box suggestions welcome !).
The basic design concept (oversimplified) is: For each page, you have one or
more objects and those objects may have content in one or more languages.
create table langtest(
pageid text not null,
objectid text not null ,
objectlang text not null,
objectdata text not null);
create unique index on (pageid,objectid,objectlang);
insert into langTest(pageID,objectID,objectLang,objectData) values
('zzz','abc','en','Lorem ipsum dolor sit amet');
insert into langTest(pageID,objectID,objectLang,objectData) values
('zzz','abc','de','Amet sit dolor ipsum lorem');
insert into langTest(pageID,objectID,objectLang,objectData) values
('zzz','def','en','Dolor ipsum amet sit lorem');
select distinct on(objectid)objectid,objectlang,pageid,objectdata from langTest
where pageid='zzz' and objectLang = any('{de,en}'::text[]) order by
objectid,array_position('{de,en}'::text[],objectLang);
(The idea being that the select query will be wrapped into a function which the
frontend will call, passing a list of elegible languages as input)
Thanks !
Laura
Re: Overriding natural order of query results for a subset
Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Saturday, 29 May 2021 17:55, Tom Lane wrote: > Michael Nolan htf...@gmail.com writes: > > > You probably need some kind order by case when else end clause, > > where the else clause deals with the non-VIPs, probably negating the need > > for a nulls last clause. > > The idiomatic way to do this, assuming that you create an "is_vip bool" > field or some other way to identify VIPs accurately, is > > ORDER BY is_vip DESC, last_name, first_name > > relying on the fact that bool TRUE > bool FALSE. > > regards, tom lane Thanks tom ! I think yours combined with Adrian's "DEFAULT of 0" is likely to be the winner. Doing some experimenting, it gives me three options: - Leave field as default = default name alphabetic - Add VIPs with same integer = VIPs at the top, ordered alphabetically - Add VIPs with differing integers = VIPs ordered by protocol Thanks all.
Re: Overriding natural order of query results for a subset
‐‐‐ Original Message ‐‐‐ On Saturday, 29 May 2021 17:06, Adrian Klaver wrote: > On 5/29/21 9:00 AM, Laura Smith wrote: > > > I did try "nulls last" but will give it another go, maybe I messed up on > > the ordering of clauses. > > Unless the fields you are ordering on contain NULLs I'm not sure how > this is going to deal with your issue. > Reading between the lines of the poster who suggested it, I'm guessing the suggestion was to add an "int" column, most of which is null except for numbers where needed for ordering and then having "order by vip_num_order,order by last_name" in my select clause.
Re: Overriding natural order of query results for a subset
I did try "nulls last" but will give it another go, maybe I messed up on the ordering of clauses. Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Saturday, 29 May 2021 15:23, Michael van der Kolff wrote: > Have you considered use of the "nulls last" option in order by > (https://www.postgresql.org/docs/13/queries-order.html)? > > Alternatively, you could write your own type, with its own ordering primitive > > > On Sun, 30 May 2021, 12:15 am Laura Smith, > wrote: > > > Hi > > > > I've got a bit of a puzzle that I'm not quite sure how to approach. > > > > Let's say I've got a table of bios, so : > > > > create table bios ( > > first_name text not null, > > last_name text not null, > > person_title text, > > person_short_bio text > > ); > > > > Now, the "natural order" would be a standard "select * from bios order by > > last_name". Basic stuff, no problem. > > > > The problem is that my use-case calls for a scenario where due to protocol > > certain people may be designated as "VIP" and therefore need to appear at > > the top. In addition, protocol may dictate that those "VIP" people > > themselves may (sometimes but not always) need to be ordered in a specific > > manner. > > > > Bear in mind that there may be a large enough number of people in this > > table that the naïve approach of manually assigning everyone an order is > > neither practical or desirable. Hence the need for an "override" which > > would mean only a subset of people would need specific parameters. > > > > Any ideas ? > > > > Thanks ! > > > > Laura
Overriding natural order of query results for a subset
Hi I've got a bit of a puzzle that I'm not quite sure how to approach. Let's say I've got a table of bios, so : create table bios ( first_name text not null, last_name text not null, person_title text, person_short_bio text ); Now, the "natural order" would be a standard "select * from bios order by last_name". Basic stuff, no problem. The problem is that my use-case calls for a scenario where due to protocol certain people may be designated as "VIP" and therefore need to appear at the top. In addition, protocol may dictate that those "VIP" people themselves may (sometimes but not always) need to be ordered in a specific manner. Bear in mind that there may be a large enough number of people in this table that the naïve approach of manually assigning everyone an order is neither practical or desirable. Hence the need for an "override" which would mean only a subset of people would need specific parameters. Any ideas ? Thanks ! Laura
Re: Modelling versioning in Postgres
Thanks both for the interesting idea of using tsrange, but also for introducing me to EXCLUDE USING GIST, I had never heard of it before. Have a good weekend ‐‐‐ Original Message ‐‐‐ On Friday, 28 May 2021 14:13, Michael van der Kolff wrote: > One thing you could consider is a range type for your "versionTS" field > instead of a single point in time. > > So that would be: > > CREATE TABLE objects ( > objectID uuid, > versionID uuid, > validRange tsrange, > objectData text, > ); > > See https://www.postgresql.org/docs/12.5/rangetypes.html for more information. > > In particular, you can enforce the obvious business rule, that there is no > objectID with overlapping validRanges (as long as you have the btree_gist > extension): > > CREATE EXTENSION btree_gist; > CREATE TABLE objects ( > objectID uuid, > versionID uuid, > validRange tsrange, > objectData text, > EXCLUDE USING GIST(objectID WITH =, validRange WITH &&) > ); > > On Fri, May 28, 2021 at 8:20 PM Laura Smith > wrote: > > > Hi > > > > I was wondering what the current thinking is on ways to model versioning in > > Postgres. > > > > The overall premise is that the latest version is the current version > > unless a rollback has occurred, in which case versions get tracked from the > > rollback point (forking ?). > > > > My initial naïve starting point is something along the lines of : > > > > create table objects ( > > objectID uuid, > > versionID uuid, > > versionTS timestamp > > objectData text > > ); > > > > This obviously creates a fool-proof answer to "latest version is the > > current version" because its a simple case of an "where objectID=x order by > > versionTS desc limit 1" query. However it clearly doesn't cover the > > rollback to prior scenarios. > > > > I then though about adding a simple "versionActive boolean". > > > > But the problem with that is it needs hand-holding somewhere because there > > can only be one active version and so it would introduce the need for a > > "active switch" script somewhere that activated the desired version and > > deactivated the others. It also perhaps is not the right way to deal with > > tracking of changes post-rollback. > > > > How have others approached the problem ? > > > > N.B. If it makes any difference, I'm dealing with a 12.5 install here, but > > this could easily be pushed up to 13 if there are benefits. > > > > Thanks for your time. > > > > Laura
Modelling versioning in Postgres
Hi I was wondering what the current thinking is on ways to model versioning in Postgres. The overall premise is that the latest version is the current version unless a rollback has occurred, in which case versions get tracked from the rollback point (forking ?). My initial naïve starting point is something along the lines of : create table objects ( objectID uuid, versionID uuid, versionTS timestamp objectData text ); This obviously creates a fool-proof answer to "latest version is the current version" because its a simple case of an "where objectID=x order by versionTS desc limit 1" query. However it clearly doesn't cover the rollback to prior scenarios. I then though about adding a simple "versionActive boolean". But the problem with that is it needs hand-holding somewhere because there can only be one active version and so it would introduce the need for a "active switch" script somewhere that activated the desired version and deactivated the others. It also perhaps is not the right way to deal with tracking of changes post-rollback. How have others approached the problem ? N.B. If it makes any difference, I'm dealing with a 12.5 install here, but this could easily be pushed up to 13 if there are benefits. Thanks for your time. Laura
Re: Storage and querying of filesystem paths
‐‐‐ Original Message ‐‐‐ On Monday, November 30, 2020 12:36 PM, Laurenz Albe wrote: > On Mon, 2020-11-30 at 12:05 +0000, Laura Smith wrote: > > > How have others approached the problem of storing and querying filesystem > > paths ? > > I'm talking about *nix/*nux style paths, e.g. /home/foo/bar > > A text column with a GIN index is an obvious possibility, but I'm > > interested to hear what approach others have taken ? > > I have not personally taken that approach, but you could give the > ltree extension a go. > > Yours, > Laurenz Albe > > --- > > Cybertec | https://www.cybertec-postgresql.com Thanks Laurenz. Will take a look at ltree
Storage and querying of filesystem paths
Hi, How have others approached the problem of storing and querying filesystem paths ? I'm talking about *nix/*nux style paths, e.g. /home/foo/bar A text column with a GIN index is an obvious possibility, but I'm interested to hear what approach others have taken ? Laura
Re: Inline count on a query
On Wednesday, 19 August 2020 15:09, David G. Johnston
wrote:
> On Wednesday, August 19, 2020, Laura Smith
> wrote:
>
> > Hi,
> >
> > Let's say we've got a fairly basic table :
> >
> > create table networks (
> > lan_id text not null,
> > net_id text not null,
> > port_id text not null
> > );
> > create index net_uniq on networks(lan_id,port_id);
> >
> > The query conundrum I am facing is that I need to add metadata to the
> > output of the query that indicates the count of ports a given net has on a
> > lan.
> >
> > So, for example, given :
> > insert into networks(lan_id,net_id,port_id) values('L1','N1,'P1');
> > insert into networks(lan_id,net_id,port_id) values('L1','N1,'P2');
> >
> > The metadata count would be 1, 2 (because 'N1' has 'P1' and 'P2' on 'L1').
> >
> > Is there a sensible way to query this without stressing out Postgres too
> > much ? I'm guessing a CTE of some sort ?
>
> Suggest you provide your desired output in table format, and show “the query”
> that you mention.
>
> David J.
If I knew what "the query" was, I wouldn't be posting here. ;-p
The only thing I've managed to come up with so far is adding count(*) over
(partition by digest(lan_id||net_id,'sha256')) to my query, but that obviously
gives the total count, not the ongoing incremental count.
Inline count on a query
Hi,
Let's say we've got a fairly basic table :
create table networks (
lan_id text not null,
net_id text not null,
port_id text not null
);
create index net_uniq on networks(lan_id,port_id);
The query conundrum I am facing is that I need to add metadata to the output of
the query that indicates the count of ports a given net has on a lan.
So, for example, given :
insert into networks(lan_id,net_id,port_id) values('L1','N1,'P1');
insert into networks(lan_id,net_id,port_id) values('L1','N1,'P2');
The metadata count would be 1, 2 (because 'N1' has 'P1' and 'P2' on 'L1').
Is there a sensible way to query this without stressing out Postgres too much ?
I'm guessing a CTE of some sort ?
Laura
Re: Changing from security definer to security invoker without dropping ?
Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Thursday, 11 June 2020 08:39, Pavel Stehule wrote: > Hi > > čt 11. 6. 2020 v 9:29 odesílatel Laura Smith > napsal: > > > Hi, > > > > Just curious if there is a way to switch a function from definer to invoker > > without dropping ? > > create function foo(a int) returns int as $$ begin return $1; end $$ language > plpgsql; > > postgres=# alter function foo (int) security definer; > ALTER FUNCTION > postgres=# alter function foo (int) security invoker; > ALTER FUNCTION > > regards > > Pavel Thanks Pavel ! Didn't realise it was that easy.
Changing from security definer to security invoker without dropping ?
Hi, Just curious if there is a way to switch a function from definer to invoker without dropping ? We're working on improving the security posture by changing functions from definer to invoker, but I'm wondering what the best way to roll this out to production is given that dropping and re-creating functions could potentially cause upstream client hassles ? Laura
"INSERT ON CONFLICT UPDATE" - Use of indexes ?
Hi, What'st the current state of play with indexes and ON CONFLICT ? The docs seem to vaguely suggest it is possible, but this SO question (https://stackoverflow.com/questions/38945027/) seems to suggest it is not. I've got a unique named index on a table (i.e. "create unique index xyz...") but I cannot seem to be able to refer to it in a function ? ON CONFLICT (index_name) : does not work ON CONFLICT ON CONSTRAINT index_name: does not work
Re: Postgres 12 RLS
On Monday, 8 June 2020 12:42, Paul Förster wrote: > Hi Laura, > > > On 08. Jun, 2020, at 12:46, Laura Smith n5d9xq3ti233xiyif...@protonmail.ch > > I had a lightbulb moment just now and tried that, but it doesn't seem to be > > working. > > The app returns "pg_execute(): Query failed: ERROR: permission denied for > > table" > > This is despite me: > > • Changing to SECURITY INVOKER on the PG function. > > • Granting the app user relevant perms on the underlying table > > • Re-granting execute for the app on the function > > Am I missing somehthing ? > > another possibility maybe is to use session_user instead of current_user in > your policy. > > current_user name user name of current execution context > session_user name session user name > > The latter is the name of the user who actually started the session. So it > should be myappuser in your case. > > https://www.postgresql.org/docs/current/functions-info.html > > Cheers, > Paul Thanks Paul, will experiment with session_user. But actually I found the solution, the function I was testing was using "INSERT ON CONFLICT UPDATE". And it seems that requires SELECT permissions due to "ON CONFLICT" (appuser was previously only granted INSERT and UPDATE).
Re: Postgres 12 RLS
On Monday, 8 June 2020 11:25, Paul Förster wrote: > Hi Laura, > > > On 08. Jun, 2020, at 12:17, Laura Smith n5d9xq3ti233xiyif...@protonmail.ch > > $$ LANGUAGE plpgsql SECURITY DEFINER; > > you might want to use security invoker instead of definer. > > https://www.postgresql.org/docs/current/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY > > https://www.cybertec-postgresql.com/en/abusing-security-definer-functions/ > > Cheers, > Paul Hi Paul, I had a lightbulb moment just now and tried that, but it doesn't seem to be working. The app returns "pg_execute(): Query failed: ERROR: permission denied for table" This is despite me: • Changing to SECURITY INVOKER on the PG function. • Granting the app user relevant perms on the underlying table • Re-granting execute for the app on the function Am I missing somehthing ?
Postgres 12 RLS
Hi, I'm having a little trouble with RLS in Postgres 12, although first time I've used RLS, so it might just be me ! The problem is that I am calling a function from a web-app, but the function seems to be executing as "postgres" even thouhg the web-app logs in as a completely different role ? This means that current_user in the function resolves to "postgres" instead of the app user. This is an example of a function : create function addses(p_regid text,p_msgid text,p_reqid text) returns integer AS $$ BEGIN UPDATE foo_regs set reg_aws_ses_msgid=p_msgid,reg_aws_amzn_requestid=p_reqid where uuid=p_regid; IF FOUND THEN return 1; ELSE return 0; END IF; END; $$ LANGUAGE plpgsql SECURITY DEFINER; grant execute on function addses(p_regid text,p_msgid text,p_reqid text) to myappuser; The foo_regs table has the following RLS policy: Policies: POLICY "foo_regs_policy" USING (((event_id = CURRENT_USER) AND (reg_hide IS FALSE)))
Re: Postgres12 - Confusion with pg_restore
Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Friday, 5 June 2020 19:35, Tom Lane wrote: > Laura Smith n5d9xq3ti233xiyif...@protonmail.ch writes: > > > But doesn't the second half of my original post demonstrate that I tried > > that very thing ? I did try creating the database first, but pg_restore > > just complained even more ? > > There are two ways you can do this: > > 1. Create the new database by hand (with CREATE DATABASE) and tell > pg_restore to restore into it. In this case you don't say -C > to pg_restore, and your -d switch points at the DB to restore into. > > 2. Have pg_restore issue CREATE DATABASE. In this case you do use > the -C switch, and your -d switch has to point at some pre-existing > database that pg_restore can connect to for long enough to issue the > CREATE DATABASE. > > You can't mix-and-match these approaches. > > regards, tom lane > Thanks for the clarificaiton Tom. All working now !
Re: Postgres12 - Confusion with pg_restore
Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Friday, 5 June 2020 19:23, Christophe Pettus wrote: > > On Jun 5, 2020, at 11:20, Laura Smith n5d9xq3ti233xiyif...@protonmail.ch > > wrote: > > sudo -u postgres pg_restore -v -C -d foobar 4_foobar_pgdump_Fc > > You need to connect to a database that already exists (such as "postgres"); > it then creates the database you are restoring and switches to it. The > relevant manual line is: > > "When (-C / --create) is used, the database named with -d is used only to > issue the initial DROP DATABASE and CREATE DATABASE commands. All data is > restored into the database name that appears in the archive." > > But doesn't the second half of my original post demonstrate that I tried that very thing ? I did try creating the database first, but pg_restore just complained even more ?
Postgres12 - Confusion with pg_restore
According to the all-mighty manual (https://www.postgresql.org/docs/current/app-pgrestore.html), life is supposed to be as simple as: "To drop the database and recreate it from the dump: $ dropdb mydb $ pg_restore -C -d postgres db.dump" The reality seems to be somewhat different ? sudo -u postgres pg_restore -v -C -d foobar 4_foobar_pgdump_Fc pg_restore: connecting to database for restore pg_restore: error: connection to database "foobar" failed: FATAL: database "foobar" does not exist So I thought I would try to create the database manually first (CREATE DATABSE ). That made pg_restore even more angry: sudo -u postgres pg_restore -C -d foobar 4_foobar_pgdump_Fc pg_restore: while PROCESSING TOC: pg_restore: from TOC entry 3088; 1262 43395 DATABASE foobar postgres pg_restore: error: could not execute query: ERROR: database "foobar" already exists Command was: CREATE DATABASE foobar WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_GB.UTF-8' LC_CTYPE = 'en_GB.UTF-8'; pg_restore: warning: errors ignored on restore: 1
Re: Conditional return of aggregated data
> > or this > with t as > (select (select sum(statcount) from stats_residence) as aggstat, > statcount,short_name_en from stats_residence > ) > select * from t where aggstat > some_number > > Apology if I did not understand the question correctly. Hi Ravi, Thanks for helping show me the way. You're quite right, a CTE did the trick Laura
Conditional return of aggregated data
Hi, I have some aggregated statistics which are currently being queried as follows: create view stats_age as SELECT a.category, a.statcount, b.a_desc FROM reg_aggregated_stats a,regs_select_age b where a.category='age' and a.datapoint::smallint=b.a_val order by a.datapoint asc; However, as these relate to event registrations, a suggestion has been made that the database really should be returning nothing until a certain number of registrations has been met (in order to avoid privacy infringing inferrence from what should be an otherwise fully irreversibly aggregated dataset). Now, the queries are wrapped in PL/PGSQL functions anyway, so I could make a second call to Postgres to find out sum(statcount) and then conditionally return based on that. But is there a smarter way to do this out of a single SQL query ? My initial idea was something along the lines of : select (select sum(statcount) from stats_residence) as aggstat,statcount,short_name_en from stats_residence where aggstat>some_number; But as I soon discovered that's not valid syntax! Hence ideas welcome from those smarter than me. Thanks ! Laura
Guidance needed on an alternative take on common prefix SQL
Hi, I've seen various Postgres examples here and elsewhere that deal with the old common-prefix problem (i.e. "given 1234 show me the longest match"). I'm in need of a bit of guidance on how best to implement an alternative take. Frankly I don't quite know where to start but I'm guessing it will probably involve CTEs, which is an area I'm very weak on. So, without further ado, here's the scenario: Given an SQL filtering query output that includes the following column: 87973891 87973970 87973971 87973972 87973973 87973975 87973976 87973977 87973978 87973979 8797400 The final output should be further filtered down to: 87973891 8797397 8797400 i.e. if $last_digit is present 0–9 inclusive, recursively filter until the remaining string is all the same (i.e. in this case, when $last_digit[0-9] is removed, 8797397 is the same). So, coming back to the example above: 8797397[0-9] is present so the "nearest common" I would be looking for is 8797397 because once [0-9] is removed, the 7 is the same on the preceeding digit. The other two rows ( 87973891 and 8797400) are left untouched because $last_digit is not present in [0-9]. Hope this question makes sense ! Laura
Re: Where to find postgresql libs in the APT repo ?
Sent with ProtonMail Secure Email. ‐‐‐ Original Message ‐‐‐ On Sunday, April 14, 2019 8:34 PM, Adrian Klaver wrote: > On 4/14/19 12:09 PM, Laura Smith wrote: > > > Hi, > > Am looking to compile some software that will make use of Postgresql > > libraries. > > However doing "apt-cache search postgresql-11-*" I can't see any libs or > > dev package ? > > postgresql-server-dev-11 - development files for PostgreSQL 11 > server-side programming > > libpq-dev - header files for libpq5 (PostgreSQL library) > libpq5 - PostgreSQL C client library > > > Would installing "postgresql-client-11" be sufficient ? I don't really > > want to go installing the whole shebang. > > Thanks ! > > Laura > > -- > > Adrian Klaver > adrian.kla...@aklaver.com Awesome ! Thank you !
Where to find postgresql libs in the APT repo ?
Hi, Am looking to compile some software that will make use of Postgresql libraries. However doing "apt-cache search postgresql-11-*" I can't see any libs or dev package ? Would installing "postgresql-client-11" be sufficient ? I don't really want to go installing the whole shebang. Thanks ! Laura
