Spaces before newlines in view definitions in 9.3

From: Joe Abbate <jma(at)freedomcircle(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Spaces before newlines in view definitions in 9.3
Date: 2013-09-10 21:21:58
Message-ID: 522F8D76.8010400@freedomcircle.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Example test code:

$ psql pyrseas_testdb
psql (9.3.0)
Type "help" for help.

pyrseas_testdb=# create table t1 (c1 int, c2 text);
CREATE TABLE
pyrseas_testdb=# create view v1 as select * from t1;
CREATE VIEW
pyrseas_testdb=# \d+ v1
View "public.v1"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+----------+-------------
c1 | integer | | plain |
c2 | text | | extended |
View definition:
SELECT t1.c1,
t1.c2
FROM t1;

It may not be immediately obvious but there is a space after the
"t1.c1," and before the first newline. In 9.2 and previous releases,
the view definition is:

SELECT t1.c1, t1.c2
FROM t1;

If there are more columns, there's an extra space for each except the
last one, e.g., (with _ denoting a trailing space):

SELECT t2.c1,_
t2.c2,_
t2.c3,_
t2.c4
FROM t2;

The problem is that the string comes back, e.g., from pg_get_viewdef()
with those extra spaces before the newlines, e.g.,

" SELECT t1.c1, \n t1.c3 * 2 AS mc3\n FROM t1;

and YAML has a way displaying a text string nicely so that it can be
recovered when it's read back, but it *doesn't* work if there are
invisible characters such as tabs or spaces before a newline because
obviously one can't tell how many or of what kind they are.

Note: This applies to both views and materialized views.

I believe the reformatting of view text (breaking each column on a
separate line) was done to improve readability but it has the side
effect of making the text unreadable if processed via a YAML utility
such as Pyrseas dbtoyaml (since YAML then quotes the entire string and
even breaks it down further with extra backslashes).

Regards,

Joe

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Bruce Momjian 2013-09-10 23:04:14 Re: BUG #7844: xpath missing entity decoding - bug or feature
Previous Message Euler Taveira 2013-09-10 21:08:19 Re: BUG #8442: Postgresql Crash Frequently and It is installed on m68k platorm