Re: Is there a way to change email for subscription ?
Hi back to you. I would unsubscribe the undesired email from the mailing-list, and then subscribe the desired email. Simple as that! Hope this help you. On 2024-09-05 12:09 a.m., Muhammad Ikram wrote: Hi, I want to change email for my PostgreSQL community subscriptions. Is there a way to do it without unsubscribing and then subscribing to a new email ? -- Regards, Muhammad Ikram -- */ArbolOne ™/* Using Fire Fox and Thunderbird. ArbolOne is composed of students and volunteers dedicated to providing free services to charitable organizations. ArbolOne's development on Java, PostgreSQL, HTML and Jakarta EE is in progress [ í ]
Using left joins instead of inner joins as an optimization
Hi All! I'm currently writing a view that joins many tables and I was wondering how PostgreSQL optimizes projections on such a view. In particular I was wondering if it is a correct and valid optimization technique to use left joins when they are equivalent to an inner join. I have created a minimal example. Suppose we have two tables: > create table a(a_id int primary key generated always as identity, a_data text); > create table b(b_id int primary key generated always as identity, a_id int not null references a(a_id), b_data text); Then the query > explain select b_id from b natural left join a; results in a `Seq Scan on b`. Whereas the query > explain select b_id from b natural join a; results in a join with sequential scans on both a and b. I believe because b.a_id is not null and references a.a_id a left and an inner join are exactly equivalent. My questions are: - Am I wrong that in such a situation a left and inner join are equivalent? - Why does PostgreSQL not automatically optimize this? - Is it a bad idea to use left joins to optimize this even if semantically an inner join would be correct? Thank you for your help!
Re: Is there a way to change email for subscription ?
I believe that's the only way to do it. On Thu, Sep 5, 2024 at 12:09 PM Muhammad Ikram wrote: > > Hi, > > I want to change email for my PostgreSQL community subscriptions. Is there > a way to do it without unsubscribing and then subscribing to a new email ? > > -- > Regards, > Muhammad Ikram > >
Re: Issue with Restore dump with plpythonu, plpython3u installed on postgres16
Thanks Adrian, I have tried your previous workaround and it worked fine. I will try this approach as well. Regards, Samson G On Thu, Sep 5, 2024 at 3:50 AM Adrian Klaver wrote: > On 9/4/24 10:46, Sam Son wrote: > > Hi Adrian, > > > > Thanks for your suggestions. I will try your modifications and do > > benchmarking. > > I got to thinking and realized I missed an important part about > separating the schema and data restores using the -s and -a arguments. > This is best explained here: > > https://www.postgresql.org/docs/current/app-pgrestore.html > > --section=sectionname > > Only restore the named section. The section name can be pre-data, > data, or post-data. This option can be specified more than once to > select multiple sections. The default is to restore all sections. > > The data section contains actual table data as well as large-object > definitions. Post-data items consist of definitions of indexes, > triggers, rules and constraints other than validated check constraints. > Pre-data items consist of all other data definition items. > > > With the modification I suggested the -s argument will result in: > > -s > --schema-only > > Restore only the schema (data definitions), not data, to the extent > that schema entries are present in the archive. > > This option is the inverse of --data-only. It is similar to, but > for historical reasons not identical to, specifying --section=pre-data > --section=post-data. > > The issue being it includes post-data definitions as in: > > "Post-data items consist of definitions of indexes, triggers, rules and > constraints other than validated check constraints. " > > That means when you restore the output of pg_restore -a the above items > will be in place and will run. Among other things if there are trigger > functions using plpython3u and said functions are not Python3 valid they > will fail. You might also get warnings like: > > " > pg_dump: warning: there are circular foreign-key constraints on this table: > pg_dump: detail: equipment > pg_dump: hint: You might not be able to restore the dump without using > --disable-triggers or temporarily dropping the constraints. > pg_dump: hint: Consider using a full dump instead of a --data-only dump > to avoid this problem. > " > > You might be better off using something like: > > pg_restore ... --section=pre-data -f ddl_defs.sql > Search/replace ddl_defs.sql > psql ... -f ddl_defs.sql > pg_restore ... --section=data > pg_restore ... --section=post-data > > > > > > > Thanks, > > Samson G > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
barman with postgres server/s
Does anyone know a best practice when it comes to installing barman? Would barman be on its own system or does it make sense to have it running on a cascaded postgres server? I'm just getting started with it so I'd like to see how others have implemented its use in a variety of different implementations of postgres servers or server. Thank you
Re: Using left joins instead of inner joins as an optimization
On Fri, 6 Sept 2024 at 23:05, Xavier Solomon wrote: > > create table a(a_id int primary key generated always as identity, a_data > > text); > > create table b(b_id int primary key generated always as identity, a_id int > > not null references a(a_id), b_data text); > > Then the query > > explain select b_id from b natural left join a; > results in a `Seq Scan on b`. Whereas the query > > explain select b_id from b natural join a; > results in a join with sequential scans on both a and b. > > I believe because b.a_id is not null and references a.a_id a left and an > inner join are exactly equivalent. Mostly equivalent, but there are a few corner cases where they're not. > My questions are: > - Am I wrong that in such a situation a left and inner join are equivalent? The foreign key triggers are deferred until at least the end of the statement, so there are cases where the foreign key can be temporarily violated. For example, if the outer query is an UPDATE a SET a_id = ... and you have an AFTER UPDATE ON a trigger that runs your left join query, a_id will be changed but the changes won't have been verified (or cascaded) in the referencing table. > - Why does PostgreSQL not automatically optimize this? We would need to change the way foreign keys work or maybe at least give the planner more context as to where the query it's planning is coming from. I think it might be safe to do this optimisation if it's a top-level query, but not if it's being run from a trigger. Maybe there are other cases which are safe too. > - Is it a bad idea to use left joins to optimize this even if semantically an > inner join would be correct? If you only ever do it in top-level statements, then I think it's safe. If not, you might get wrong results. For deferred foreign key constraints, the window where the foreign key could be violated lasts until the end of the transaction, so even top-level queries could see wrong results if you use left join instead of inner. David
Re: Using left joins instead of inner joins as an optimization
On Fri, Sep 6, 2024 at 7:05 AM Xavier Solomon wrote: > > explain select b_id from b natural left join a; > results in a `Seq Scan on b`. Whereas the query > > explain select b_id from b natural join a; > results in a join with sequential scans on both a and b. > I think your example is a little too contrived. Try explaining select * from b natural left join a; and you should see the plans become equivalent again. I would expect a query that left joins but only pulls data from one table to be not feasible in real life. Yes, in an ideal world the non-left join would be smart enough to not even do the scan on a, but it's kind of a moot point outside of odd select clauses. - Is it a bad idea to use left joins to optimize this even if semantically > an inner join would be correct? > Not at all - if it works for you, go ahead. But I'm dubious you will gain much for queries that actually make use of the left join, at least for relatively simply selects. Cheers, Greg
Re: Is there a way to change email for subscription ?
On 2024-Sep-05, Muhammad Ikram wrote: > I want to change email for my PostgreSQL community subscriptions. Is there > a way to do it without unsubscribing and then subscribing to a new email ? Yes. You need to add your new email address as a secondary here https://www.postgresql.org/account/profile/ Then go to https://lists.postgresql.org/manage/ and click on "Edit subscription" for each list, and select which email address you want to use. In the first page you can also change which address is your primary and which are secondary. -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ "La gente vulgar sólo piensa en pasar el tiempo; el que tiene talento, en aprovecharlo"
Re: Issue with Restore dump with plpythonu, plpython3u installed on postgres16
On 9/6/24 05:58, Sam Son wrote: Thanks Adrian, I have tried your previous workaround and it worked fine. I will try this approach as well. If the previous solution(-s/-a) works then my update should work also. I offered it as possible solution should you run into out of sequence issues using -s/-a. Regards, Samson G -- Adrian Klaver adrian.kla...@aklaver.com
Foreign Data Wrappers
I find myself in new territory, Foreign Data Wrappers (FDW). I stumble closer to success through the documentation, youtube videos, and various google resources. But I've come across a concept that intrigues me very deeply. If I understand the concepts correctly, FDW not only makes other databases available, FDW also offers access to .csv files, plain text, or just about anything that can be bullied into some kind of query-able order. Has anyone ever tried to connect to redis or elasticache? If so, how did it work out? Regards, Gus -- Gus gus.sp...@gmail.com 540-454-3074 “Characteropathic individuals adopt ideologies created by doctrinaire, often schizoidal people, recast them into an active propaganda form, and disseminate it with their characteristic pathological egotism and paranoid intolerance for any philosophies which may differ from their own.” (*Political Ponerology*, Andrew Lobaczewski, 1984)
Re: Foreign Data Wrappers
> On Sep 6, 2024, at 17:55, Gus Spier wrote: > If I understand the concepts correctly, FDW not only makes other databases > available, FDW also offers access to .csv files, plain text, or just about > anything that can be bullied into some kind of query-able order. There two parts to FDWs: The code in the PostgreSQL core, and the FDW extension that is specific to type of the remote data source. It's up to the plug-in to translate the remote data source into a format that appears as a foreign table, so the specifics of how the remote data source are mapped to the table depend on the plug-ins implementation. You need to install the FDW extension in the PostgreSQL instance as you do with any extension. There are a lot of different plug-ins, of varying degrees to maintenance, out in the ecosystem. They definitely exist for Redis; I'm not sure about Elasticache.
Re: Foreign Data Wrappers
2024年9月7日(土) 9:55 Gus Spier : > > I find myself in new territory, Foreign Data Wrappers (FDW). I stumble closer > to success through the documentation, youtube videos, and various google > resources. But I've come across a concept that intrigues me very deeply. > > If I understand the concepts correctly, FDW not only makes other databases > available, FDW also offers access to .csv files, plain text, or just about > anything that can be bullied into some kind of query-able order. Has anyone > ever tried to connect to redis or elasticache? If so, how did it work out? There are two FDW implementations listed for Redis here: https://wiki.postgresql.org/wiki/Foreign_data_wrappers#NoSQL_Database_Wrappers but neither have been updated for recent PostgreSQL versions. Regards Ian Barwick
Re: Foreign Data Wrappers
Gus Spier writes: > If I understand the concepts correctly, FDW not only makes other databases > available, FDW also offers access to .csv files, plain text, or just about > anything that can be bullied into some kind of query-able order. Has anyone > ever tried to connect to redis or elasticache? If so, how did it work out? Looks like it's been done: https://wiki.postgresql.org/wiki/Foreign_data_wrappers (No warranty expressed or implied on the quality of these particular FDWs.) regards, tom lane
Re: Foreign Data Wrappers
Thanks for the pointers! I'll resume my task in the morning. If I get anywhere, I will post the outcome. R, Gus On Fri, Sep 6, 2024 at 9:35 PM Tom Lane wrote: > Gus Spier writes: > > If I understand the concepts correctly, FDW not only makes other > databases > > available, FDW also offers access to .csv files, plain text, or just > about > > anything that can be bullied into some kind of query-able order. Has > anyone > > ever tried to connect to redis or elasticache? If so, how did it work > out? > > Looks like it's been done: > > https://wiki.postgresql.org/wiki/Foreign_data_wrappers > > (No warranty expressed or implied on the quality of these > particular FDWs.) > > regards, tom lane >
Re: Foreign Data Wrappers
The most interesting (IMHO) FDW implementation I have used is an Australian WFS service mounted locally as a Postgis table. Not wonderful performance, but full spatial query functionality. If performance was an issue, "create table as select * from FDW;" creates a local cache... Very useful functionality. Brent Wood On Saturday, September 7, 2024 at 12:55:37 PM GMT+12, Gus Spier wrote: I find myself in new territory, Foreign Data Wrappers (FDW). I stumble closer to success through the documentation, youtube videos, and various google resources. But I've come across a concept that intrigues me very deeply. If I understand the concepts correctly, FDW not only makes other databases available, FDW also offers access to .csv files, plain text, or just about anything that can be bullied into some kind of query-able order. Has anyone ever tried to connect to redis or elasticache? If so, how did it work out? Regards,Gus -- Gus gus.spier@gmail.com540-454-3074 “Characteropathic individuals adopt ideologies created by doctrinaire, often schizoidal people, recast them into an active propaganda form, and disseminate it with their characteristic pathological egotism and paranoid intolerance for any philosophies which may differ from their own.” (Political Ponerology, Andrew Lobaczewski, 1984)
Re: barman with postgres server/s
Hi, I had the opportunity to work with barman. In my opinion, to configure barman, you should have a separate dedicated system. Configure WAL archiving properly to ensure that all transaction logs are streamed to Barman like the following archive_command = 'rsync %p barman@backup_server:/path/to/backup/%f' Regular monitoring is required. You can check status via barman check command Use streaming replication by using this command 'streaming_archiver=on' in the barman.conf file. On Fri, 6 Sept 2024 at 18:24, postgr user wrote: > Does anyone know a best practice when it comes to installing barman? > > Would barman be on its own system or does it make sense to have it running > on a cascaded postgres server? > > I'm just getting started with it so I'd like to see how others have > implemented its use in a variety of different implementations of postgres > servers or server. > > > Thank you >
