Re: [GENERAL] Explanantion on pgbouncer please

Where are you measuring the connections? From your app to PGBouncer, or from PGBouncer to PostgreSQL? If it is from your app to PGBouncer, that sounds strange, and like the app is not properly releasing connections as it should. If it is from PGBouncer to PostgreSQL, that sounds normal. I

Re: [GENERAL] Explanantion on pgbouncer please

For where you are measuring, everything looks normal to me. Your application will make connections to the pooler as needed, and the pooler will assign the application connection to a database connection it has available in it's pool. This gets rid of the overhead of creating a brand new

Re: [GENERAL] table versioning approach (not auditing)

Felix, I'd love to see a single, well maintained project. For example, I just found yours, and gave it a shot today after seeing this post. I found a bug when an update command is issued, but the old and new values are all the same. The trigger will blow up. I've got a fix for that, but if we

Re: [GENERAL] table versioning approach (not auditing)

will be easy, as it offers the same functions than JSON afaik. Gesendet: Dienstag, 30. September 2014 um 21:16 Uhr Von: Adam Brusselback adambrusselb...@gmail.com An: Felix Kunde felix-ku...@gmx.de Cc: pgsql-general@postgresql.org pgsql-general@postgresql.org Betreff: Re: [GENERAL] table versioning

Re: [GENERAL] table versioning approach (not auditing)

of testing in that direction. I will transfer the code soon to a more public repo on GitHub. As far as I see I have to create an organization for that. Cheers Felix *Gesendet:* Mittwoch, 01. Oktober 2014 um 17:09 Uhr *Von:* Adam Brusselback adambrusselb...@gmail.com *An:* Felix Kunde felix-ku

Re: [GENERAL] table versioning approach (not auditing)

Ended up running for 28 min, but it did work as expected. On Thu, Oct 2, 2014 at 10:27 AM, Adam Brusselback adambrusselb...@gmail.com wrote: Testing that now. Initial results are not looking too performant. I have one single table which had 234575 updates done to it. I am rolling back

Re: [GENERAL] pgAgent

Here you are: do $$ declare job_id int; begin /* add a job and get its id: */ insert into pgagent.pga_job ( jobjclid , jobname ) values ( 1 /*1=Routine Maintenance*/ , 'DELETE_NAMES' /* job name */ ) returning

Re: [GENERAL] Using the database to validate data

A little late to the party, but i'll share how I do my data imports / validation for anyone interested. I have a bunch of data that comes in from various sources, and it isn't always guaranteed to be in the correct format, have the right foreign keys, or even the right data types. I have a

Re: [GENERAL] PostgreSQL Developer Best Practices

Personally I always set the natural key with a not null and unique constraint, but create an artificial key for it as well. As an example, if we had a product table, the product_sku is defined as not null with a unique constraint on it, while product_id is the primary key which all other tables

[GENERAL] UUID and Enum columns in exclusion constraints

Just wondering what others have done for using enum or uuid columns in exclusion constraints? I have a solution now, but I just wanted to see what others have ended up doing as well and see if what i'm doing is sane. If i'm doing something unsafe, or you know of a better way, please chime in.

Re: [GENERAL] OT hardware recommend

Agreed with Joshua, a single ssd will have way more performance than all 15 of those for random io for sure, and probably be very close on sequential. That said, a raid controller able to handle all 15 drives (or multiple that handle a subset of the drives) is likely to be more expensive than a

Re: [GENERAL] Plan to support predicate push-down into subqueries with aggregates?

I responded yesterday, but it seems to have gotten caught up because it was too big with the attachments... Here it is again. Sorry about not posting correctly, hopefully I did it right this time. So I wanted to see if Sql Server (2014) could handle this type of query differently than Postgres

Re: [GENERAL] Plan to support predicate push-down into subqueries with aggregates?

Rob, I understand that if I were to replicate the logic in that view for every use case I had for those totals, this would not be an issue. But that would very much complicate some of my queries to the point of absurdity if I wanted to write them in a way which would push everything down properly.

Re: [GENERAL] Plan to support predicate push-down into subqueries with aggregates?

to contain that logic within a view of some sort though, as a bunch of other stuff is built on top of that. Having to push that aggregate query into all of those other queries would be hell. Thanks, -Adam On Tue, Mar 8, 2016 at 5:17 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Adam Br

[GENERAL] Plan to support predicate push-down into subqueries with aggregates?

Hi all. I was wondering if there were any plans to support predicate push-down optimization for subqueries (and views) with aggregates? I was recently bit by this, as I had incorrectly assumed that this was an optimization that was in place, and designed quite a bit around that assumption, only

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

>It is not difficult to simulate column store in a row store system if >you're willing to decompose your tables into (what is essentially) >BCNF fragments. It simply is laborious for designers and programmers. I could see a true column store having much better performance than tricking a row

Re: [GENERAL] first_value/last_value

Is there a reason you can't do that now with a limit 1/order by/union all? Just have it ordered one way on the first query and the other on the bottom. That will give you two rows that are the first / last in your set based on whatever column you order on. On May 18, 2016 8:47 PM, "Tom Smith"

Re: [GENERAL] first_value/last_value

Here is an example that works in a single query. Since you have two different orders you want the data back in, you need to use subqueries to get the proper data back, but it works, and is very fast. CREATE TEMPORARY TABLE foo AS SELECT generate_series as bar FROM generate_series(1, 100);

Re: [GENERAL] Foreign key triggers

Yes, foreign keys are implemented using triggers. Here is a blog post explaining a little more: http://bonesmoses.org/2014/05/14/foreign-keys-are-not-free/ I would assume it's still got to do a seq scan even on every referencing table even if it's empty for every record since there are no

Re: [GENERAL] Thoughts on "Love Your Database"

> On Tue, May 17, 2016 at 1:54 PM, Raymond O'Donnell wrote: > > Having said all that, I've rarely had any trouble with pgAdmin 3 on > > Windows 7 and XP, Ubuntu and Debian; just a very occasional crash (maybe > > one every six months). So just to chime in, it has not been at all

Re: [GENERAL] C++ port of Postgres

Just wondering what the end goal is for this project... Is it to just maintain an up to date Postgres fork that will compile with a C++ compiler? Is it to get a conversation going for a direction for Postgres itself to move? The former I don't see gaining much traction or doing all that much for

Re: [GENERAL] create trigger in postgres to check the password strength

Whoops, accidentally sent this to only Pawan instead of the list: > > Hey there, so I would highly suggest you avoid arbitrary password strength policies like that. I wrote a library for my company which we use for password strength estimation, but it is written in Java. I've been thinking about

Re: [GENERAL] create trigger in postgres to check the password strength

Oh sorry, I misunderstood. Didn't realize you meant database users an not an application user table implemented in Postgres. I'll let others answer that then because i'm not aware of a way to do that.

Re: [GENERAL] Search on very big (partitioned) table

Do you have non overlapping check constraints on the partitions by date to allow the planner to exclude the child tables from needing to be looked at?

Re: [GENERAL] Vacuum Full - Questions

Yes that very well could happen because the size of the table changed, as well as stats being more accurate now. Just because you have a seq scan doesn't mean the planer is making a bad choice.

Re: [GENERAL] Incrementally refreshed materialized view

On Mon, Sep 26, 2016 at 2:35 PM, Rob Sargent wrote: > Of course 9.5 is the current release so the answer is Yes, since 9.5 > > It seems like there is some confusion about what we're talking about. I am talking about incremental updates to a sort of "fake" materialized view

[GENERAL] Incrementally refreshed materialized view

Hello all, I am working on a plan to implement incrementally refreshed materialized "views" with the existing functionality in Postgres. Below is the plan for doing that: Trigger based eagerly updated materialized tables for Postgres 9.5 > > > > High level plan: > > Have a view definition stored

Re: [GENERAL] Incrementally refreshed materialized view

I require eagerly refreshed materialized views for my use case, which is something Postgres does not currently support. I need my updates to a table the view refers to visible within the same transaction, and often it is a single change to one row which will only effect a single row in the view.

Re: [GENERAL] Incrementally refreshed materialized view

On Mon, Sep 26, 2016 at 3:21 PM, Kevin Grittner wrote: > On Mon, Sep 26, 2016 at 2:04 PM, Rakesh Kumar > wrote: > > > Does PG have a concept of MV log, from where it can detect the > > delta changes and apply incremental changes quickly. > > That

Re: [GENERAL] Incrementally refreshed materialized view

On Thu, Sep 29, 2016 at 8:10 AM, Nguyễn Trần Quốc Vinh wrote: > Dear, > > As it was recommended, we pushed our projects into github: > https://github.com/ntqvinh/PgMvIncrementalUpdate. > > 1) Synchronous incremental update > - For-each-row triggers are generated for all

Re: [GENERAL] Foreign key against a partitioned table

I have wondered if there were any plans to enhance fkey support for partitioned tables now that more work is being done on partitioning (I know there has been a large thread on declarative partitioning on hackers, though I haven't followed it too closely). Foreign keys are all done through

Re: [GENERAL] CRM where pg is a first class citizen?

On Tue, Dec 13, 2016 at 3:36 PM, George Weaver wrote: > I've never used it but what about: > > https://developer.sugarcrm.com/2012/08/03/like-postgresql- > and-want-to-use-it-with-sugarcrm-check-out-a-new-community-project/ > > Cheers, > George Looks like not much came out of

Re: [GENERAL] Column Tetris Automatisation

I for one would love having something like this available. I also know i've seen discussed in the past, divorcing the physical column order from the logical column order, which seems like it'd be useful here as well to not break the workflow of those who do use ordinal positions for columns.

Re: [GENERAL] Materialised view - refresh

You can use something like cron, windows task scheduler (if you're running windows), pgagent (or jpgagent), pg_cron, or any of the others. I personally use (and wrote) jpgagent, at the time pgagent was the only alternative and it was very unstable for me. Here is the link if interested:

Re: [GENERAL] Caching and Blobs in PG? Was: Can PG replace redis, amqp, s3 in the future?

> > there's also pg_agent which is a cron-like extension, usually bundled with > pg_admin but also available standalone > > https://www.pgadmin.org/docs4/dev/pgagent.html > > > -- > john r pierce, recycling bits in santa cruz > In addition to that, there is also jpgAgent:

Re: [GENERAL] looking for a globally unique row ID

> > I cannot image a single postgres index covering more than one physical > table. Are you really asking for that? While not available yet, that is a feature that has had discussion before. Global indexes are what i've seen it called in those discussions. One of the main use cases is to

Re: [GENERAL] looking for a globally unique row ID

Here is the last discussion I saw on it: https://www.postgresql.org/message-id/flat/90261791-b731-a516-ab2a-dafb97df4464%40postgrespro.ru#90261791-b731-a516-ab2a-dafb97df4...@postgrespro.ru

Re: [GENERAL] Porting libpq to QNX 4.25

> > Do you believe the only path is Windows 10? Not Peter...But I believe there are better choices considering if this has to be on any sort of network, you're going to be potentially vulnerable to any number of never-to-be-patched security holes. Using XP in this day and age is a hard argument

Re: [GENERAL] Postgres 10.1 fails to start: server did not start in time

Hey Christoph, I tried starting it with init (service postgresql start), and pg_ctlcluster. I modified the pg_ctl.conf and set the timeout higher so I could just get my cluster back up and running properly, so I can't give you the info on what systemctl status says at the moment. On Sat, Nov

[GENERAL] Postgres 10.1 fails to start: server did not start in time

Hey all, I am in the process of upgrading to Postgres 10, and am having trouble getting my streaming replica working. OS: Debian 9.2 Version: 10.1 I have my primary backed up using pgbackrest, and I restore that to my replica. It generates a recovery.conf which has a restore command for the WAL

Re: [GENERAL] Postgres 10.1 fails to start: server did not start in time

> You might want to increase pg_ctl's wait timeout for this situation, > since the default's evidently too little. However ... Got it, thanks. > ... pg_ctl itself wouldn't decide to forcibly shut down the server > if the timeout expired. It merely stops waiting and tells you so. > It seems like

[GENERAL] Possible bug: could not open relation with OID [numbers] SQL State: XX000

Hey all, First off: PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit I have something going on, and i'm not sure what is causing it. I recently upgraded our development environment to PG10, and the error in the subject appeared with one of my

Re: [GENERAL] Possible bug: could not open relation with OID [numbers] SQL State: XX000

I believe it's one of the temp tables. The oid changes each time the function is run. I'll put some logging in place to identify the exact temp table it is though.

Re: [GENERAL] Possible bug: could not open relation with OID [numbers] SQL State: XX000

Alright I figured it out. The OID does not match any of the temp tables, so not sure what's up there. I have the function RETURN QUERY, and then I drop all my temp tables. If I don't drop the tmp_base table at the end of the function, it will work just fine. If I keep the drop at the end in

Re: [GENERAL] Possible bug: could not open relation with OID [numbers] SQL State: XX000

Huh, so in the other cases where the function works fine, it's likely that the data all just fits within the regular table and doesn't have to be TOAST'ed? So this is something that isn't changed in PG10, and I could have encountered in 9.6, and just by chance didn't? This is a pattern I've used

Re: [GENERAL] Possible bug: could not open relation with OID [numbers] SQL State: XX000

> If that's the correct theory, yes. Did you match up the OID yet? Yes, I did just now. The OID matches the TOAST table for the temp table: contract_actual_direct. This just really surprises me I haven't seen it before considering I know for a fact that some of my other functions are way more

Re: [GENERAL] Migrating money column from MS SQL Server to Postgres

> Since you are migrating data into a staging table in PostgreSQL, you may set > the field data type as TEXT for each field where you have noticed or > anticipate issues. > Then after population perform the datatype transformation query on the given > fields to determine the actual field value

[GENERAL] UPDATE syntax change

Hey all, just getting around to updating my development environment to Postgres 10, and there was something I found while in testing. Version info: PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit I have some queries that were working in 9.6 which

Re: [GENERAL] UPDATE syntax change (column-list UPDATE syntax fails with single column)

Appreciate the link, didn't come up when I was googling the issue. As you said, a mention in the release notes would have been helpful. Thanks, -Adam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] Alternative to pgAdmin Postgres Manager that support pgagent (jobs)

Happy to hear jpgAgent is working alright for you. If you have any questions with it feel free to ask me. If you do want to help with pgAutomator, that sounds like something you could start to learn on. jpgAgent is pretty much feature complete as far as my needs go, and no one has requested any

Re: [GENERAL] Alternative to pgAdmin Postgres Manager that support pgagent (jobs)

I'm currently in the same boat that I wish there was something better for running jobs against Postgres than pgAgent. Using pgAdmin to manage my numerous jobs isn't the best experience i've ever had to say the least, but it does work. No other tool I have used will manage pgAgent jobs. I worked