Re: [HACKERS] MERGE SQL Statement for PG11
PMFJI > We seem to have a few options for PG11 > > 1. Do nothing, we reject MERGE > > 2. Implement MERGE for unique index situations only, attempting to > avoid errors (Simon OP) > > 3. Implement MERGE, but without attempting to avoid concurrent ERRORs > (Peter) > > 4. Implement MERGE, while attempting to avoid concurrent ERRORs in > cases where that is possible. >From an end-users point of view I would prefer 3 (or 4 if that won't prevent this from going into 11) INSERT ... ON CONFLICT is great, but there are situations where the restrictions can get in the way and it would be nice to have an alternative - albeit with some (documented) drawbacks. As far as I know Oracle also doesn't guarantee that MERGE is safe for concurrent use - you can still wind up with a unique key violation. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Burst in WAL size when UUID is used as PK while full_page_writes are enabled
akapila wrote: > You might want to give a try with the hash index if you are planning > to use PG10 and your queries involve equality operations. But you can't replace the PK index with a hash index, because hash indexes don't support uniqueness. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CTE inlining
> Just to se what other RDBMS are doing with CTEs; Look at slide > 31 here: > https://www.percona.com/live/17/sites/default/files/slides/Recursive%20Query%20Throwdown.pdf That is taken from Markus Winand's post: https://twitter.com/MarkusWinand/status/852862475699707904 "Seems like MySQL is getting the best WITH support of all tested DBs (RECURSIVE not tested!)" -- View this message in context: http://www.postgresql-archive.org/CTE-inlining-tp5958992p5961164.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CTE inlining
> 1) we switch unmarked CTEs as inlineable by default in pg11. +1 from me for option 1 -- View this message in context: http://www.postgresql-archive.org/CTE-inlining-tp5958992p5959615.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CTE inlining
> I could tolerate telling people to use OFFSET 0 (and documenting it!) > as a workaround if we can't get something more friendly in. I agree with that. > If we go with WITH INLINE then we're likely not solving anything, because > most people will simply use WITH just like now, and will be subject to the > fencing without realizing it. I agree - the default behaviour should be change to match what everybody expects. The current behaviour should be the exception. > Yes, and we're missing the opportunity to confirm with what other > systems do, and the spirit of the SQL language's declare what I want, > not how to do it, model. Essentially *all* other systems optimize CTEs the same way they optimize derived tables. I think even MySQL does it like that in the upcoming 8.0 release. I have never heard anyone saying that the Postgres implementation is an advantage and that they would hate to see this disappear. I usually hear "Why is Postgres doing that? Can't they change that?" Maybe I have a limited view on this, but from where I stand, simply changing it would help everybody I know and would not break anything. I don't even think a replacement for the old behaviour would be necessary. -- View this message in context: http://www.postgresql-archive.org/CTE-inlining-tp5958992p5959509.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CTE inlining
> Relevant posts where users get confused by our behaviour: > And Markus Winand's blog: http://modern-sql.com/feature/with/performance Databases generally obey this principle, although PostgreSQL represents a big exception and Besides PostgreSQL, all tested databases optimize with clauses in the same way that they optimize views or derived tables" -- View this message in context: http://www.postgresql-archive.org/CTE-inlining-tp5958992p5959313.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GSoC 2017
Greg Stark wrote > I don't think this even needs to be tied to currencies. I've often > thought this would be generally useful for any value with units. This > would prevent you from accidentally adding miles to kilometers or > hours to parsecs which is just as valid as preventing you from adding > CAD to USD. There is already such a concept - not tied to currencies or units in general. The SQL standard calls it DISTINCT types. And it can prevent comparing apples to oranges. I don't have the exact syntax at hand, but it's something like this: create distinct type customer_id_type as integer; create distinct type order_id_type as integer; create table customers (id customer_id_type primary key); create table orders (id order_id_type primary key, customer_id customer_id_type not null); And because those columns are defined with different types, the database will refuse to compare customers.id with orders.id (just like it would refuse to compare an integer with a date). So an accidental join like this: select * from orders o join customers c using (id); would throw an error because the data types of the IDs can not be compared. -- View this message in context: http://postgresql.nabble.com/GSoC-2017-tp5938331p5941383.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: WG: [HACKERS] Packages: Again
Wolfgang Wilhelm wrote > - The more difficult a database change including rewriting of code will > get the less likely you'll find something paying for it. In my case there > is a list of reasons from the customer _not_ to switch from Oracle to > PostgreSQL. Besides more obvious reasons like APEX applications on the > list there are things like "complicated PL/SQL code e.g. ... packages..." > (whatever complicated is). Lots of the other reasons on that list begin to > blur because of the changes of the recent versions or the near future like > parallelisation or working on partitions. > Of course there are some questions about style, maintainability... But > this would be another post. We are a similar shop: mostly Oracle and increasingly more Postgres. But we essentially stopped (or are in the process of) using packages altogether - /because/ of maintainability. If a package contains more then just a single procedure it's impossible for two devs to work on different procedures because the package body still needs to be a *single* source file (which sometimes means: a single file with 10 or 20 procedures). Wherever we have the chance we started migrating packages into standalone procedures. Which is a bit cumbersome given Oracle's limit on 30 characters for identifiers - but it still increases maintainability. And one of the advantages given for packages was the increase in namespace availability which is much easier with Postgres anyway. Just my 0.02€ -- View this message in context: http://postgresql.nabble.com/Packages-Again-tp5938583p5938892.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UNDO and in-place update
> FWIW, while this is basically true, the idea of repurposing UNDO to be > usable for MVCC is definitely an Oracleism. Mohan's ARIES paper says > nothing about MVCC. > For snapshot isolation Oracle has yet a *third* copy of the data in a > space called the "rollback segment(s)". UNDO and rollback segment are the same thing in Oracle. In older versions it was just called "rollback segment" I think it started with Oracle 10g that they called it UNDO. > Fwiw, Oracle does not use the undo log for snapshot fetches. > It's used only for transaction rollback and recovery. As UNDO and "rollback" are the same they do use the UNDO information for MVCC: http://docs.oracle.com/database/121/CNCPT/consist.htm#GUID-00A3688F-1219-423C-A5ED-4B8F25BEEAFB__BABFDBAJ -- View this message in context: http://postgresql.nabble.com/UNDO-and-in-place-update-tp5931575p5931844.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: session server side variables
Pavel Stehule wrote > Session server side variables are one major missing feature in PLpgSQL. I think this would also be useful outside of PL/pgSQL to support query level variables similar to what SQL Server does. -- View this message in context: http://postgresql.nabble.com/proposal-session-server-side-variables-tp5925827p5925836.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [GENERAL] pg_upgrade from 9.5 to 9.6 fails with "invalid argument"
Masahiko Sawada schrieb am 30.09.2016 um 12:54:
I did this on two different computers, one with Windows 10 the other with
Windows 7.
(only test-databases, so no real issue anyway)
In both cases running a "vacuum full" for the table in question fixed the
problem and pg_upgrade finished without problems.
Because vacuum full removes the _vm file, pg_upgrade completed job successfully.
If you still have the _vm file
("d:/Daten/db/pgdata95/base/16410/85358_vm") that lead an error, is it
possible that you check if there is '\r\n' [0d 0a] character in that
_vm file or share that _vm file with us?
Yes, I saved one of the clusters.
The file can be downloaded from here: http://www.kellerer.eu/85358_vm
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: integration bloat tables (indexes) to core
Tom Lane-2 wrote > The problem with an extension is: when we make a core change that breaks > one of these views, which we will, how can you pg_upgrade a database > with the extension installed? There's no provision for upgrading an > extension concurrently with the core upgrade. Maybe there should be, > but I'm unclear how we could make that work. > > At the same time, I'm pretty suspicious of putting stuff like this in > core, because the expectations for cross-version compatibility go up > by orders of magnitude as soon as we do that. Why not provide a "SQL" or "Admin Scripts" directory as part of the installation that contains community "recommended" scripts for things like that? As those aren't extensions or somehow part of the data directory they don't need to be migrated and pg_upgrade does not need to take care of that. When installing a new version, the new scripts that work with the new version are installed automatically but will not overwrite the old version's scripts as the new version typically is stored in a different directory. -- View this message in context: http://postgresql.nabble.com/proposal-integration-bloat-tables-indexes-to-core-tp5907511p5908273.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Draft release notes for next week's releases
Oleg Bartunov-2 wrote > But still, icu provides us abbreviated keys and collation stability, Does include ICU mean that collation handling is identical across platforms? E.g. a query on Linux involving string comparison would yield the same result on MacOS and Windows? If that is the case I'm all for it. Currently the different behaviour in handling collation aware string comparisons is a bug in my eyes from a user's perspective. I do understand and can accept the technical reasons for that, but it still feels odd that a query yields different results (with identical data) just because it runs on a different platform. -- View this message in context: http://postgresql.nabble.com/Draft-release-notes-for-next-week-s-releases-tp5895357p5895484.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [JDBC] 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102
Robert Haas wrote:
> This isn't the first complaint about this mechanism that we've gotten,
> and it won't be the last. Way too many of our users are way more
> aware than they should be that the threshold here is five rather than
> any other number, which to me is a clear-cut sign that this needs to
> be improved. How to improve it is a harder question. We lack the
> ability to do any kind of sensitivity analysis on a plan, so we can't
> know whether there are other parameter values that would have resulted
> in a different plan, nor can we test whether a particular set of
> parameter values would have changed the outcome.
(I initially posted that question on the JDBC mailing list)
To be honest: looking at the efforts Oracle has done since 9 up until 12 I
am not sure this is a problem that can be solved by caching plans.
Even with the new "in-flight" re-planning in Oracle 12 ("cardinality
feedback") and all the effort that goes into caching plans we are still
seeing similar problems with (prepared) statements that are suddenly slow.
And as far as I can tell, the infrastructure around plan caching,
invalidation, bind variable peeking and all that seems to be a *lot* more
complex ("sophisticated") in Oracle compared to Postgres. And the results
don't seem to justify the effort (at least in my experience).
With all the problems I have seen (in Oracle and Postgres) I think that
maybe a better solution to this problem is to make the planner fast (and
reliable) enough so that plan caching isn't necessary in the first place.
However I have no idea how feasible that is.
--
View this message in context:
http://postgresql.nabble.com/Fwd-JDBC-Re-9-4-1207-behaves-differently-with-server-side-prepared-statements-compared-to-9-2-1102-tp5881825p5882835.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] No Issue Tracker - Say it Ain't So!
> And email integration for Jira is nonexistant. That is not true. We do have an email integration where customers can create issues by sending an email to a specific "Jira Email" address. And as far as I know this is a standard module from Atlassian. I _think_ it can also be configured that you can reply to the notification emails and the reply will be added as a comment to the issue. > like that you can't have more than one person assigned to a bug That is true, there is only one "Assignee" for an issue - the person who is responsible for it. But given the flexibility of Jira I'm pretty sure one could configure an additional field (e.g. "is being worked on by" that can be assigned multiple users. One thing that is indeed still missing is a Git integration the way the Subversion integration works. Jira scans the commit messages for ticket numbers and automatically links an issue to the commits. Thomas (Note that I'm not in any way affiliated with Atlassian - just to avoid that impression) -- View this message in context: http://postgresql.nabble.com/No-Issue-Tracker-Say-it-Ain-t-So-tp5867020p5867093.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] No Issue Tracker - Say it Ain't So!
> We have to use something OSS; open source projects depending on > closed-source infra is bad news. Out of what's available, I'd actually > choose Bugzilla; as much as BZ frustrates the heck out of me at times, > it's the only OSS tracker that's at all sophisticated. There are several OSS projects that use closed-source trackers. I (personally) don't see a real problem with that. Atlassian offers free hosting for open source projects (I'm sure Postgres would qualify) and Confluence Jira is one of the best trackers I have worked with. I does have a mail gateway were issues can be created and maintained by sending emails (rather than editing them in the web front end) They also support Postgres as their backend (and you do find hints here and there that it is the recommended open source DBMS for them - but they don't explicitly state it like that). We are using Jira at the company I work for and all Jira installations run on Postgres there. https://www.atlassian.com/software/views/open-source-license-request Thomas -- View this message in context: http://postgresql.nabble.com/No-Issue-Tracker-Say-it-Ain-t-So-tp5867020p5867046.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Materialized views don't show up in information_schema
> Does someone know what other DBMSs do in this regard? I.e., do they > put anything in INFORMATION_SCHEMA.VIEWS for matviews? What TABLE_TYPE > do they use in INFORMATION_SCHEMA.TABLES? I can only speak for Oracle. Oracle doesn't have INFORMATION_SCHEMA but their JDBC driver treats mviews as tables. If you ask the driver for a list of tables (explicitly passing "TABLE" as the only type to return), it will include mviews as well, returning "MATERIALIZED VIEW" as the type of the object. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Materialized-views-don-t-show-up-in-information-schema-tp5822643p5823559.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] parametric block size?
> Possibly stopping at the tablespace level might be more straightforward.
> To avoid messing up the pages in shared buffers we'd perhaps need
> something like several shared buffer pools - each with either its own
> blocksize or associated with a (set of) tablespace(s).
This is exactly how Oracle does it. You can specify the blocksize when
creating a tablespace.
For each blocksize a separate buffer cache ("shared buffers" in Postgres
terms) can be configured. So the cache is not maintained on tablespace level
but on blocksize level.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/parametric-block-size-tp5812350p5813060.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] New shapshot RPMs (Mar 27, 2009) are ready for testing
Dave Page, 30.03.2009 14:28: On Mon, Mar 30, 2009 at 1:06 PM, Thomas Kellerer wrote: OK, thanks. I received very strange error messages last week when I accessed that page. (Velocity Template not found and similar errors). But now it's working. Yeah, we had a big website update and that page got messed up along the way somehow. Sorry 'bout that. No problem. I'm glad you are supplying those builds! Regards Thomas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
