Re: [HACKERS] MERGE SQL Statement for PG11

2017-11-03 Thread Thomas Kellerer
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

2017-10-27 Thread Thomas Kellerer
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

2017-05-12 Thread Thomas Kellerer
> 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

2017-05-04 Thread Thomas Kellerer
> 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

2017-05-03 Thread Thomas Kellerer
> 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

2017-05-02 Thread Thomas Kellerer
> 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

2017-01-27 Thread Thomas Kellerer
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

2017-01-13 Thread Thomas Kellerer
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

2016-11-24 Thread Thomas Kellerer
> 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

2016-10-14 Thread Thomas Kellerer
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"

2016-09-30 Thread Thomas Kellerer

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

2016-06-16 Thread Thomas Kellerer
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

2016-03-28 Thread Thomas Kellerer
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

2016-01-18 Thread Thomas Kellerer
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!

2015-09-24 Thread Thomas Kellerer
>  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!

2015-09-23 Thread Thomas Kellerer
> 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

2014-10-18 Thread Thomas Kellerer
 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?

2014-07-28 Thread Thomas Kellerer
 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

2009-03-30 Thread Thomas Kellerer

Dave Page, 30.03.2009 14:28:

On Mon, Mar 30, 2009 at 1:06 PM, Thomas Kellerer spam_ea...@gmx.net 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