[GENERAL] Rules, Windows and ORDER BY

storage in Postgres. The window functions make, alas, no use of indexes. -- Jason Dusek pgp // solidsnack // C1EBC57DC55144F35460C8DF1FD4C6C1FED18A2B EXPLAIN (COSTS FALSE, FORMAT YAML) SELECT * FROM kv WHERE k = ... AND realm = ... ORDER BY at LIMIT 1; -[ RECORD 1

Re: [GENERAL] Rules, Windows and ORDER BY

2012/8/23 Tom Lane t...@sss.pgh.pa.us: Jason Dusek jason.du...@gmail.com writes: I have a simple table of keys and values which periodically receives updated values. It's desirable to keep older values but, most of the time, we query only for the latest value of a particular key. CREATE

Re: [GENERAL] logger table

so far been fine without primary keys. (I keep only an hour there at a time, though, and it's only a few hundred megs.) In the past, I have had trouble maintaining a high TPS while having lots (hundreds) of connected clients; maybe you'll want to use a connection pool. -- Jason Dusek pgp

Re: [GENERAL] Syncing an application cache with xmin

2013/2/3 Tom Lane t...@sss.pgh.pa.us: Jason Dusek jason.du...@gmail.com writes: The idea would be, to store information about the last XID in the last sync and search for XIDs committed since then upon reconnecting for sync. Perhaps `txid_current_snapshot()' preserves enough information

Re: [GENERAL] date_trunc to aggregate values?

out of this set, with the 'avg column representing the mean of the first and last four of each 15 minute records. Suggestions? Are you using an explicit GROUP BY? -- Jason Dusek pgp // solidsnack // C1EBC57DC55144F35460C8DF1FD4C6C1FED18A2B -- Sent via pgsql-general mailing list (pgsql

[GENERAL] Analyzing the types of prepared statements

prefer to be able to run the analysis step on a DB without changing it. -- Jason Dusek pgp // solidsnack // C1EBC57DC55144F35460C8DF1FD4C6C1FED18A2B -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref

Re: [GENERAL] Analyzing the types of prepared statements

2013/3/19 John DeSoi de...@pgedit.com: On Mar 16, 2013, at 8:30 AM, Jason Dusek jason.du...@gmail.com wrote: However, it is not clear to me at this juncture how to get the return type for a statement, given its text. Preparing and looking it up in pg_prepared_statements will retrieve

Re: [GENERAL] regclass and format('%I')

On 14 March 2015 at 09:17, David G. Johnston david.g.johns...@gmail.com wrote: On Saturday, March 14, 2015, Jason Dusek jason.du...@gmail.com wrote: It honestly seems far more reasonable to me that %s and %I should do the exact same thing with regclass. My reasoning is as follows: ‘%I

Re: [GENERAL] regclass and format('%I')

that imposing the burden of choosing between `%s` and `%I` opens up the possibility of confusing vulnerabilities. Kind Regards, Jason Dusek

[GENERAL] regclass and format('%I')

SQL identifiers, so it stands to reason that `%I` should result in a valid identifier for both of them (or neither one). Kind Regards, Jason Dusek -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref

Re: [GENERAL] regclass and format('%I')

‘%I’ should do nothing. On 13 March 2015 at 12:42, David G. Johnston david.g.johns...@gmail.com wrote: On Fri, Mar 13, 2015 at 12:18 PM, Jason Dusek jason.du...@gmail.com wrote: Hi All, The difference in how format handles `regclass` and `name` seems like an inconsistency

Re: [GENERAL] Joins between foreign tables

stand to reason that the IN list could be pushed down to the foreign server; but this query’s performance is no better than the direct join between table_on_server1 and table_on_server2. Best Regards, Jason Dusek ​

[GENERAL] Joins between foreign tables

Regards, Jason Dusek ​

[GENERAL] There can be only one

Consider a table of providers, for which one is the default. For example, payment providers: CREATE TABLE payment_via ( iduuid PRIMARY KEY, provider text NOT NULL, keys hstore NOT NULL DEFAULT '' ); Here we store together the name of the provider — medici, paypal

[GENERAL] client_min_messages and INFO

the reverse of LOG? Kind Regards, Jason Dusek

[GENERAL] HeapTuple to JSON -- composite_to_json()?

to convert a HeapTuple to a Datum? Best Regards, Jason Dusek ​

Re: [GENERAL] HeapTuple to JSON -- composite_to_json()?

heap_copy_tuple_as_datum looks promising… http://doxygen.postgresql.org/heaptuple_8c.html#abfa9096cd7909cb17a6acfdc7b31b7ad

Re: [GENERAL] Uber migrated from Postgres to MySQL

With regards to write amplification, it makes me think about about OIDs. Used to be, every row had an OID and that OID persisted across row versions. https://www.postgresql.org/docs/9.5/static/runtime-config-compatible.html#GUC-DEFAULT-WITH-OIDS Would reintroducing such a feature address some

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Thu, 28 Jul 2016 at 01:18 Gavin Flower <gavinflo...@archidevsys.co.nz> wrote: > On 28/07/16 17:52, Jason Dusek wrote: > > With regards to write amplification, it makes me think about about > > OIDs. Used to be, every row had an OID and that OID persisted ac

[GENERAL] Large empty table, balanced INSERTs and DELETEs, not being vacuumed

? Kind Regards, Jason Dusek ​

[GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

=# INSERT INTO with_pk VALUES (2), (2) ON CONFLICT DO NOTHING; ERROR: could not serialize access due to concurrent update =# END;ROLLBACK How are these two transactions different? Kind Regards, Jason Dusek ​

Re: [GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

│ on default_transaction_isolation │ serializable ​ On Tue, 11 Oct 2016 at 13:00 Kevin Grittner <kgri...@gmail.com> wrote: > On Tue, Oct 11, 2016 at 2:29 PM, Jason Dusek <jason.du...@gmail.com> > wrote: > > > I notice the following oddity: > > > =# CREATE TABLE with_pk (i in

Re: [GENERAL] Imperative Query Languages

They said it couldn't be done... dandl schrieb am Di. 11. Juli 2017 um 06:58: > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] On Behalf Of Merlin Moncure > > > It's probably of broader interest to consider some sort of "more > relational"

Re: [GENERAL] Imperative Query Languages

On Tue, 4 Jul 2017 at 23:01 Tom Lane wrote: > I'm pretty sure that that is the model that relational databases (and the > SQL language in particular) replaced, back in the 70s or so. Look up > "network" databases (eg CODASYL) and "hierarchical" DBs (eg IMS) for some >

[GENERAL] Imperative Query Languages

Hi All, This more of a general interest than specifically Postgres question. Are there any “semi-imperative” query languages that have been tried in the past? I’m imagining a language where something like this: for employee in employees: for department in department: if

Re: [GENERAL] Imperative Query Languages

On Tue, 4 Jul 2017 at 23:22 Chris Travers wrote: > Having done a lot of SQL optimisation stuff I have doubts that this is > possible. The problem is that it is much easier to go from a declarative > to an imperative plan than it is to go the other way. In fact

Re: [GENERAL] Imperative Query Languages

On Tue, 4 Jul 2017 at 23:57 Chris Travers chris.trav...@gmail.com wrote: I am curious where you see LINQ as starting at an imperative syntax. > The imperative integration is thin, I admit — it just the integration with for loops. Here's a good case that

Re: [GENERAL] Imperative Query Languages

On Wed, 5 Jul 2017 at 14:36 Peter Geoghegan p...@bowt.ie wrote: However, the OP seemed to be describing something that maps imperative > code to a declarative SQL query or something equivalent, which isn't > quite the same thing. The declarative nature of SQL feels