Re: [HACKERS] System catalog vacuum issues
I used to use VACUUM FULL periodically to resolve the issue, but the
problem arises again in 2-3 months.
Here is the statistics (from pgstattuple). I run VACUUM FULL on 2013-08-07.
dcdb=# select date, relpages, reltuples, table_len, tuple_count,
tuple_percent, dead_tuple_count, dead_tuple_len, free_space,
free_percent, autovacuum_count from public.table_statistics where
relname = 'pg_attribute' order by date;
date| relpages | reltuples | table_len | tuple_count |
tuple_percent | dead_tuple_count | dead_tuple_len | free_space |
free_percent | autovacuum_count
+--+---+---+-+---+--+++--+--
2013-08-08 |39029 |109096 | 319725568 | 37950 |
1.66 |52540 |7355600 | 296440048 |92.72
| 6359
2013-08-09 |12382 | 95848 | 101433344 | 38232 |
5.28 |57443 |8042020 | 83862864 |82.68
| 6711
2013-08-10 |11365 |105073 | 93102080 | 37789 |
5.68 |65599 |9183860 | 74483104 | 80
| 7002
2013-08-12 | 9447 | 95289 | 77389824 | 37811 |
6.84 |57154 |8001560 | 60479736 |78.15
| 7161
2013-08-13 |47841 | 82877 | 391913472 | 38536 |
1.38 |30461 |4264540 | 369093756 |94.18
| 7347
2013-08-14 |70265 |104926 | 575610880 | 38838 |
0.94 |34649 |4850860 | 546449480 |94.93
| 7398
(6 rows)
Autovacuum is running on this table, however it keeps growing.
On 08/06/2013 09:35 PM, Tom Lane wrote:
Vlad Arkhipov writes:
On 08/06/2013 04:26 PM, Sergey Konoplev wrote:
What pgstattuple shows on this table?
dcdb=# select * from pgstattuple('pg_catalog.pg_attribute');
table_len | tuple_count | tuple_len | tuple_percent |
dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space |
free_percent
+-+---+---+--++++--
6363938816 | 48786 | 6830040 | 0.11 | 1459439 |
204321460 | 3.21 | 5939017376 | 93.32
(1 row)
So the problem isn't so much that you have lots of dead tuples, it's that
the file is full of free space. I suspect the key issue is that
autovacuum is unable to truncate the file because of too many concurrent
accesses. There was a fix in 9.2.3 that was meant to ameliorate that
problem, but maybe that's not getting the job done for you. Or maybe the
bloat we're looking at is left over from when you were running earlier
9.2.x releases; in which case a one-time VACUUM FULL should fix it.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pgstat_reset_remove_files ignores its argument
in 9.3 and 9.4, pgstat_reset_remove_files uses the global variable
pgstat_stat_directory rather than the argument it is passed, "directory".
On crash recovery, this means the tmp directory gets cleared twice and the
permanent pg_stat doesn't get cleared at all.
It seems like the obvious one line change would fix it, but I haven't
tested it because I don't know how to cause a crash without pg_stat already
being empty.
pgstat_reset_remove_files(const char *directory)
{
DIR*dir;
struct dirent *entry;
charfname[MAXPGPATH];
dir = AllocateDir(pgstat_stat_directory);
Cheers,
Jeff
Re: [HACKERS] Foreground vacuum and buffer access strategy
On Wed, Aug 14, 2013 at 1:41 AM, Greg Stark wrote: > On Tue, Aug 13, 2013 at 3:45 PM, Robert Haas wrote: >> >> I'm not sure what the right thing to do here is, but I definitely >> agree there's a problem. There are definitely cases where people want >> or indeed need to vacuum as fast as possible, and using a small ring >> buffer is not the way to do that. > > I'm not convinced using a ring buffer is necessarily that bad even if > you want to vacuum as fast as possible. The reason we use a small ring > buffer is to avoid poisoning the entire cache with vacuum pages, not > to throttle the speed of vacuum by introducing synchronous wal > flushes. > > I think we should increase the size of the ring buffer if we hit a > synchronous wal buffer flush and there is less than some amount of wal > pending. It will be better if the decision to increase ring buffer also consider other activity, otherwise it can lead to more I/O due to buffer replacements by backend. I am not sure currently there is any way to check that, but if we maintain buffers on free list, then it can be used to check the current activity (if there are enough buffers on free list, then ring size can be increased as it is an indication that the system is relatively less busy). > That amount is the relevant thing people might want to limit > to avoid slowing down other transaction commits. The walwriter might > even provide a relevant knob already for how much wal should be the > maximum pending. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.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] danger of stats_temp_directory = /dev/shm
On Tuesday, August 13, 2013, Josh Berkus wrote: > On 08/13/2013 09:57 AM, Jeff Janes wrote: > > Is this a blocker for 9.3? > > Why would it be? This issue doesn't originate with 9.3. > Before 9.3, it would delete one specific file from a potentially shared directory. In 9.3 it deletes the entire contents of a potentially shared directory. That is a massive expansion in the surface area for unintentional deletion. If we will disallow using shared directories before the time 9.3 is released, that would fix it one way, but I don't know if that is the plan or not. Cheers, Jeff
Re: [HACKERS] How to create read-only view on 9.3
Hi, (2013/08/14 5:24), Josh Berkus wrote: > On 08/13/2013 11:18 AM, Tom Lane wrote: >> Hannu Krosing writes: >>> If you earlier used views for granting limited read access to some views >>> you definitely did not want view users suddenly gain also write access to >>> underlying table. >> >> Unless you'd explicitly granted those users insert/update/delete privilege >> on the view, they wouldn't suddenly be able to do something new in 9.3, >> because no such privileges are granted by default. If you had granted >> such privileges, you don't have much of a leg to stand on for complaining >> that now they can do it. > > Ah, ok. I hadn't gotten to the testing phase yet. > > I think we should have a script available for revoking all write privs > on all views and link it from somewhere (the release notes?), but I > don't see any need to change anything in the release. > Yes, I was not thinking about changing current 9.3 behavior. So I think it's enough to know the impact and how to avoid that on the release notes. thanks a lot! regards, --- NTT Software Corporation Tomonari Katsumata -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
CREATE TRANSFORM syntax (was Re: [HACKERS] [PATCH] Add transforms feature)
On Mon, 2013-07-08 at 23:00 -0700, Hitoshi Harada wrote: > On Sun, Jul 7, 2013 at 12:06 PM, Peter Eisentraut > wrote: > > On Thu, 2013-07-04 at 02:18 -0700, Hitoshi Harada wrote: > >> as someone suggested in the previous thread, it might be a variant > of > >> CAST. CREATE CAST (hstore AS plpython2u) ? Or CREATE LANGUAGE > TRANSFORM > >> might sound better. In either case, I think we are missing the > discussion > >> on the standard overloading. > > > > LANGUAGE isn't a concept limited to the server side in the SQL > standard. > > I could go with something like CREATE SERVER TRANSFORM. > > I like it better than the current one. I had started to work on making this adjustment, but found the result very ugly. It also created a confusing association with CREATE SERVER, which is something different altogether. My next best idea is CREATE TRANSFORM FOR hstore SERVER LANGUAGE plperl, which preserves the overall idea but still distinguishes server from client languages. Comments? -- 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] Regarding BGworkers
On Tue, Aug 13, 2013 at 8:07 PM, Michael Paquier wrote: > On Tue, Aug 13, 2013 at 11:59 PM, Alvaro Herrera > wrote: >> maybe_start_bgworker() in postmaster.c >> do_start_bgworker() in postmaster.c >> StartBackgroundWorker() in bgworker.c > This formulation is fine, thanks. Instead of maybe_start_bgworker, > what about start_bgworker_if_necessary? I think Alvaro's suggestion is better. It's shorter, and makes clear that at most one will be started. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] UNNEST with multiple args, and TABLE with multiple funcs
On 08/14/2013 08:22 AM, Josh Berkus wrote: > On 08/13/2013 06:54 AM, Andrew Gierth wrote: >> Summary: >> >> This patch implements a method for expanding multiple SRFs in parallel >> that does not have the surprising LCM behaviour of SRFs-in-select-list. >> (Functions returning fewer rows are padded with nulls instead.) > > BTW, if anyone is unsure of the use-case for this, I have some uses for it: > > 1. denormalized data stored in same-length arrays (usually for > compression reasons) > > 2. use with PL/Python-Numpy and PL/R functions which return multiple > arrays or 2D arrays. > > In other words, I have *lots* of uses for this functionality, and I > think the analytics crowd will like it. Which means that I need to get > on testing it, of course ... Similarly, I see uses for this come up a lot, and usually have to work around it with ugly invocations of multiple SRFs in the SELECT list in a subquery. I was thinking of implementing multi-argument unnest directly with `any` parameters if I could get it to work, but hadn't started on it yet. This looks like a really clever approach and it handles multiple spec-compliance items. I'll grab the patch and try it out. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] UNNEST with multiple args, and TABLE with multiple funcs
On 08/13/2013 06:54 AM, Andrew Gierth wrote: > Summary: > > This patch implements a method for expanding multiple SRFs in parallel > that does not have the surprising LCM behaviour of SRFs-in-select-list. > (Functions returning fewer rows are padded with nulls instead.) BTW, if anyone is unsure of the use-case for this, I have some uses for it: 1. denormalized data stored in same-length arrays (usually for compression reasons) 2. use with PL/Python-Numpy and PL/R functions which return multiple arrays or 2D arrays. In other words, I have *lots* of uses for this functionality, and I think the analytics crowd will like it. Which means that I need to get on testing it, of course ... -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] Regarding BGworkers
On Tue, Aug 13, 2013 at 11:59 PM, Alvaro Herrera wrote: > maybe_start_bgworker() in postmaster.c > do_start_bgworker() in postmaster.c > StartBackgroundWorker() in bgworker.c This formulation is fine, thanks. Instead of maybe_start_bgworker, what about start_bgworker_if_necessary? -- Michael -- 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] Possible bug with row_to_json
On Tue, Aug 13, 2013 at 4:34 PM, Tom Lane wrote: > Since this behavior can also be demonstrated in 9.2 (and maybe further > back using xml features?), I don't think we should consider it a > blocker bug for 9.3. I'm planning to set it on the back burner for > the moment and go worry about the planner's LATERAL bugs. +1 merlin -- 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] Possible bug with row_to_json
I wrote: > Jack Christensen writes: >> It ignored the rename. > I looked into this and found that the culprit is the optimization that > skips ExecProject() if a scan plan node is not doing any useful > projection. Further poking at this issue shows that there are related behaviors that aren't fixed by my proposed patch. The original complaint can be replicated in the regression database like this: select row_to_json(i8) from (select q1 as a, q2 from int8_tbl offset 0) i8; where we'd expect row_to_json to emit column names "a"/"q2" but we actually get "q1"/"q2". But consider this variant: select row_to_json(i8) from (select q1,q2 from int8_tbl offset 0) i8(x,y); Arguably, this should show column names x/y but what you get is q1/q2, even with my patch. Related cases include select row_to_json(v) from (values(1,2) limit 1) v(x,y); select row_to_json((select i8 from int8_tbl i8(x,y) limit 1)); In the first two of those, the planner isn't bothering to install the column aliases into the plan's target lists. While we could fix that, it wouldn't help the last case, where the whole-row Var for "int8_tbl" is evaluated at scan level; the code for that is looking at the relation's tuple descriptor not the scan node's result descriptor. I'm not even sure what a clean fix for that case would look like. Since this behavior can also be demonstrated in 9.2 (and maybe further back using xml features?), I don't think we should consider it a blocker bug for 9.3. I'm planning to set it on the back burner for the moment and go worry about the planner's LATERAL bugs. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] TODO request: multi-dimensional arrays in PL/pythonU
All, Currently PL/python has 1 dimension hardcoded for returning arrays: create or replace function nparr () returns float[][] language plpythonu as $f$ from numpy import array x = ((1.0,2.0),(3.0,4.0),(5.0,6.0),) return x $f$; josh=# select nparr() ; ERROR: invalid input syntax for type double precision: "(1.0, 2.0)" CONTEXT: while creating return value PL/Python function "nparr" josh=# I'd like to add the following TODO to the TODO list: PL/Python [] Allow functions to return multi-dimensional arrays from lists or numpy arrays. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] Review: UNNEST (and other functions) WITH ORDINALITY
On Tue, Aug 13, 2013 at 8:20 PM, Robert Haas wrote: > Blech. Well, that's why we need to stop hacking the lexer before we shoot a > hole through our foot that's too large to ignore. But it's not this patch's > job to fix that problem. Hm. I thought it was. However it turns out the NULLS FIRST and the WITH* problems are not exactly analogous. Because NULLS and FIRST are both unreserved keywords whereas WITH is a reserved keyword the problems are really different. Whereas WITH can be fixed by going through all the places in the grammar where WITH appears, NULLS FIRST really can't be fixed without reserving NULLS. -- greg -- 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] How to create read-only view on 9.3
On 08/13/2013 11:18 AM, Tom Lane wrote: > Hannu Krosing writes: >> If you earlier used views for granting limited read access to some views >> you definitely did not want view users suddenly gain also write access to >> underlying table. > > Unless you'd explicitly granted those users insert/update/delete privilege > on the view, they wouldn't suddenly be able to do something new in 9.3, > because no such privileges are granted by default. If you had granted > such privileges, you don't have much of a leg to stand on for complaining > that now they can do it. Ah, ok. I hadn't gotten to the testing phase yet. I think we should have a script available for revoking all write privs on all views and link it from somewhere (the release notes?), but I don't see any need to change anything in the release. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] Foreground vacuum and buffer access strategy
On Tue, Aug 13, 2013 at 3:45 PM, Robert Haas wrote: > > I'm not sure what the right thing to do here is, but I definitely > agree there's a problem. There are definitely cases where people want > or indeed need to vacuum as fast as possible, and using a small ring > buffer is not the way to do that. I'm not convinced using a ring buffer is necessarily that bad even if you want to vacuum as fast as possible. The reason we use a small ring buffer is to avoid poisoning the entire cache with vacuum pages, not to throttle the speed of vacuum by introducing synchronous wal flushes. I think we should increase the size of the ring buffer if we hit a synchronous wal buffer flush and there is less than some amount of wal pending. That amount is the relevant thing people might want to limit to avoid slowing down other transaction commits. The walwriter might even provide a relevant knob already for how much wal should be the maximum pending. -- greg -- 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] How to create read-only view on 9.3
On Tue, Aug 13, 2013 at 10:24:32AM -0700, Josh Berkus wrote:
> All,
>
> > In any case, using permissions is a somewhat leaky bandaid, since
> > superusers have overriding access privileges anyway. A better way to do
> > what the OP wants might be to have a view trigger that raises an exception.
>
> I think it would be better to supply a script which revoked write
> permissions from all views from all users, and distribute it with
> PostgreSQL. I think that's doable as a DO $$ script.
>
> If I wrote something like that, where would we drop it?
>
> The fact that it won't revoke permissions from superusers isn't a real
> problem, IMNSHO. If anyone is relying on superusers not being able to
> do something, they're in for pain in several other areas.
>
Something like this?
DO LANGUAGE plpgsql
$$
DECLARE v TEXT;
BEGIN
FOR v IN SELECT pg_catalog.quote_ident(schemaname) || '.' ||
pg_catalog.quote_ident(viewname)
FROM pg_catalog.pg_views
WHERE schemaname NOT IN ('pg_catalog', 'information_schema') LOOP
EXECUTE 'REVOKE INSERT, UPDATE, DELETE, TRUNCATE ON ' || v || ' FROM
PUBLIC';
END LOOP;
END;
$$;
Cheers,
David.
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
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] Review: UNNEST (and other functions) WITH ORDINALITY
On Tue, Aug 6, 2013 at 6:10 PM, Greg Stark wrote: > The only other case I could come up with in my regression tests is pretty > esoteric: > > CREATE COLLATION nulls (locale='C'); > ALTER OPERATOR CLASS text_ops USING btree RENAME TO first; > CREATE TABLE nulls_first(t text); > CREATE INDEX nulls_first_i ON nulls_first(t COLLATE nulls first); > > I'm not 100% sure there aren't other cases where this can occur though. Blech. Well, that's why we need to stop hacking the lexer before we shoot a hole through our foot that's too large to ignore. But it's not this patch's job to fix that problem. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [HACKERS] How to create read-only view on 9.3
On Tue, Aug 13, 2013 at 1:18 PM, Tom Lane wrote: > There's no "security hole" here; if someone can do something that > they couldn't do before, it's because you explicitly granted them > privileges to do so. This point is completely bogus. Very, very few applications I've run across in the entirety of my career use database enforced security at all; it's generally done at the application level with the application role as owner (or perhaps even superuser). You can call people names or whatever for doing that but the point is it's common usage and people *will* be affected. > I don't think you have a lot of room to complain > if those privileges now do what the SQL standard says they should do. This point is completely correct and makes the previous argument moot. This is not a 'security hole' but an 'obfuscation hole' so automatic correction is not warranted. With the options on the table, I'd prefer doing nothing or perhaps more strongly worded note in the docs and possibly the release notes with a slight preference on doing nothing. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Release schedule for PG 9.3
It seems that the volume of 9.3-specific bug reports is tailing off. After some discussion, the core committee has agreed to produce a 9.3rc1 version next week (that is, wrap Monday the 19th for public announcement Thursday the 22nd). If no showstopper bugs are reported in the next couple of weeks, we'll wrap 9.3.0 on Monday Sept 2 for public announcement Monday Sept 9. (This scheduling leaves a couple extra days for package-making because of the proximity to the Labor Day holiday.) regards, tom lane -- 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] How to create read-only view on 9.3
On 08/13/2013 01:33 PM, Hannu Krosing wrote: In any case, using permissions is a somewhat leaky bandaid, since superusers have overriding access privileges anyway. A better way to do what the OP wants might be to have a view trigger that raises an exception. Superuser can easily disable or drop the trigger as well. That's true, but it requires positive action to do so. Thus the trigger can give you some protection in cases of stupidity, if not cases of malice. cheers andrew -- 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] How to create read-only view on 9.3
Hannu Krosing writes: > If you earlier used views for granting limited read access to some views > you definitely did not want view users suddenly gain also write access to > underlying table. Unless you'd explicitly granted those users insert/update/delete privilege on the view, they wouldn't suddenly be able to do something new in 9.3, because no such privileges are granted by default. If you had granted such privileges, you don't have much of a leg to stand on for complaining that now they can do it. I think this whole thread is nonsense. We expended a good deal of sweat in 9.3 to add a feature that's *required by SQL standard*, and now people are acting like we should turn it off. I do not believe that there are many users for which this will be a problem; and we shouldn't let one complaint drive us to do something silly. In fact, I'm not sure there are *any* users for which this is a problem. AFAICS there are two cases: 1. The view in question is owned by you. Then you have insert etc privileges on it by default, and so 9.3 will let you insert into it by default. But the view grants you no capability that you didn't have anyway, just by inserting directly into the underlying table. 2. The view in question is not owned by you. Then you don't have insert (or any other) privilege on it by default. There's no "security hole" here; if someone can do something that they couldn't do before, it's because you explicitly granted them privileges to do so. I don't think you have a lot of room to complain if those privileges now do what the SQL standard says they should do. regards, tom lane -- 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] How to create read-only view on 9.3
* Hannu Krosing (ha...@2ndquadrant.com) wrote: > If you earlier used views for granting limited read access to some views > you definitely did not want view users suddenly gain also write access to > underlying table. > > You also probably did not GRANT only SELECT to your views as this was > the default anyway, I'm not really convinced that we should be catering to this argument of "well, I knew it was gonna end up being read-only anyway, so I just GRANT'd ALL"- consider that rules can make view writable, even in existing releases. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] How to create read-only view on 9.3
On 08/13/2013 06:23 PM, Andrew Dunstan wrote: > > On 08/13/2013 12:09 PM, Merlin Moncure wrote: >> On Tue, Aug 13, 2013 at 10:12 AM, Hannu Krosing >> wrote: >>> On 08/13/2013 03:25 PM, Merlin Moncure wrote: I chatted about this on IRC for a bit. Apparently, updatability of views is a mandatory feature in the sql standard and by relying on the read-only-ness you were relying on non-standard behavior essentially. I admit this is a pretty big pain (and I'm a real stickler for backwards compatibility) but it's pretty hard to argue with the standard. Workarounds are to revoke various privileges. >>> Perhaps pg_dump from 9.3 should add REVOKE ALL ...; GRANT SELECT ...; >>> when dumping views from older postgreSQL versions ? >> I thought so initially until I learned that views are expressly >> read-write per the standard; we're not changing behavior but >> implementing required functionality. In this case implementing required functionality does change behaviour in quite substantial way. If you earlier used views for granting limited read access to some views you definitely did not want view users suddenly gain also write access to underlying table. You also probably did not GRANT only SELECT to your views as this was the default anyway, >> So (at the least) I don't think >> it's fair to expect users who don't care about this point to have to >> go re-GRANT the appropriate privs -- so if you did that I think it >> would have to be an optional switch to pg_dump. That said, it's >> pretty much a given this is going to burn some people and given the >> potential security considerations maybe some action is warranted. >> Personally, I'd be satisfied with a dump time warning though or >> perhaps a strongly worded note in the documentation? >> >> > > > In any case, using permissions is a somewhat leaky bandaid, since > superusers have overriding access privileges anyway. A better way > to do what the OP wants might be to have a view trigger that raises an > exception. Superuser can easily disable or drop the trigger as well. > > cheers > > andrew > > > > -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ -- 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] danger of stats_temp_directory = /dev/shm
On 08/13/2013 09:57 AM, Jeff Janes wrote: > Is this a blocker for 9.3? Why would it be? This issue doesn't originate with 9.3. > If it is a concern of not what is deleted but rather that someone can > inject a poisoned stats file into the directory, does it need to be > back-patched all the way, as that could be done before the split > patch? I'd say it's a backpatch. We'll need to warn the heck out of users, though. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] How to create read-only view on 9.3
All, > In any case, using permissions is a somewhat leaky bandaid, since > superusers have overriding access privileges anyway. A better way to do > what the OP wants might be to have a view trigger that raises an exception. I think it would be better to supply a script which revoked write permissions from all views from all users, and distribute it with PostgreSQL. I think that's doable as a DO $$ script. If I wrote something like that, where would we drop it? The fact that it won't revoke permissions from superusers isn't a real problem, IMNSHO. If anyone is relying on superusers not being able to do something, they're in for pain in several other areas. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] danger of stats_temp_directory = /dev/shm
On Thu, Apr 25, 2013 at 8:24 AM, Peter Eisentraut wrote: > On 4/25/13 12:09 AM, Tom Lane wrote: >> I think we need it fixed to reject any stats_temp_directory that is not >> postgres-owned with restrictive permissions. The problem here is not >> with what it deletes, it's with the insanely insecure configuration. > > Yeah, the requirements should be similar to what initdb requires for > PGDATA and pg_xlog. Is this a blocker for 9.3? If it is a concern of not what is deleted but rather that someone can inject a poisoned stats file into the directory, does it need to be back-patched all the way, as that could be done before the split patch? Cheers, Jeff -- 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] 9.3 release notes suggestions
On Tue, Aug 13, 2013 at 05:59:05PM +0900, Etsuro Fujita wrote: > > Thanks for the many suggestions on improving the 9.3 release notes. > > There were many ideas I would have never thought of. Please keep the > suggestions > > coming. > > One small suggestion: > > > > Allow foreign data > wrappers to support writes (inserts/updates/deletes) on foreign > tables (KaiGai Kohei) > > > > This is the in-core functionality, so ISTM it would be better that this is > stated in the section of Object Manipulation rather than in that of Additional > Modules. Please find attached a patch. Agreed, done. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] How to create read-only view on 9.3
On 08/13/2013 12:09 PM, Merlin Moncure wrote: On Tue, Aug 13, 2013 at 10:12 AM, Hannu Krosing wrote: On 08/13/2013 03:25 PM, Merlin Moncure wrote: I chatted about this on IRC for a bit. Apparently, updatability of views is a mandatory feature in the sql standard and by relying on the read-only-ness you were relying on non-standard behavior essentially. I admit this is a pretty big pain (and I'm a real stickler for backwards compatibility) but it's pretty hard to argue with the standard. Workarounds are to revoke various privileges. Perhaps pg_dump from 9.3 should add REVOKE ALL ...; GRANT SELECT ...; when dumping views from older postgreSQL versions ? I thought so initially until I learned that views are expressly read-write per the standard; we're not changing behavior but implementing required functionality. So (at the least) I don't think it's fair to expect users who don't care about this point to have to go re-GRANT the appropriate privs -- so if you did that I think it would have to be an optional switch to pg_dump. That said, it's pretty much a given this is going to burn some people and given the potential security considerations maybe some action is warranted. Personally, I'd be satisfied with a dump time warning though or perhaps a strongly worded note in the documentation? In any case, using permissions is a somewhat leaky bandaid, since superusers have overriding access privileges anyway. A better way to do what the OP wants might be to have a view trigger that raises an exception. cheers andrew -- 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] How to create read-only view on 9.3
On Tue, Aug 13, 2013 at 10:12 AM, Hannu Krosing wrote: > On 08/13/2013 03:25 PM, Merlin Moncure wrote: >> I chatted about this on IRC for a bit. Apparently, updatability of >> views is a mandatory feature in the sql standard and by relying on the >> read-only-ness you were relying on non-standard behavior essentially. >> I admit this is a pretty big pain (and I'm a real stickler for >> backwards compatibility) but it's pretty hard to argue with the >> standard. Workarounds are to revoke various privileges. > > Perhaps pg_dump from 9.3 should add REVOKE ALL ...; GRANT SELECT ...; > when dumping views from older postgreSQL versions ? I thought so initially until I learned that views are expressly read-write per the standard; we're not changing behavior but implementing required functionality. So (at the least) I don't think it's fair to expect users who don't care about this point to have to go re-GRANT the appropriate privs -- so if you did that I think it would have to be an optional switch to pg_dump. That said, it's pretty much a given this is going to burn some people and given the potential security considerations maybe some action is warranted. Personally, I'd be satisfied with a dump time warning though or perhaps a strongly worded note in the documentation? merlin merlin -- 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] pg_dump and schema names
On Fri, Aug 9, 2013 at 02:15:31PM -0400, Bruce Momjian wrote: > > Well, it's certainly not immediately obvious why we shouldn't merge them. > > But I would have expected the function's header comment to now explain > > that the output is intentionally not schema-qualified and assumes that the > > search path is set for the object's schema if any. > > OK, done with the attached patch. The dump output is unchanged. > > > > Also, this seems like dead code as there is no test for "INDEX" in the > > > if() block it exists in: > > > > > /* > > > * Pre-7.3 pg_dump would sometimes (not always) put a fmtId'd name > > > * into te->tag for an index. This check is heuristic, so make its > > > * scope as narrow as possible. > > > */ > > > if (AH->version < K_VERS_1_7 && > > > te->tag[0] == '"' && > > > te->tag[strlen(te->tag) - 1] == '"' && > > > strcmp(type, "INDEX") == 0) > > > appendPQExpBuffer(buf, "%s", te->tag); > > > else > > > > Huh, yeah it is dead code, since _printTocEntry doesn't call this function > > for "INDEX" objects. And anyway I doubt anybody still cares about reading > > 7.2-era archive files. No objection to removing that. > > Removed. Patch applied. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] How to create read-only view on 9.3
On 08/13/2013 03:25 PM, Merlin Moncure wrote: > On Tue, Aug 13, 2013 at 5:37 AM, Tomonari Katsumata > wrote: >> Hi Szymon, >> >> Thank you for response. >> >> Could you show an example? >> I do below things on one server. >> The path to database cluster and port are >> different with each other. >> >> [9.2.4] >> initdb --no-locale -E UTF8 >> pg_ctl start >> createdb testdb >> psql testdb -c "create table tbl(i int)" >> psql testdb -c "insert into tbl values (generate_series(1,10))" >> psql testdb -c "create view v as select * from tbl" >> >> [9.3beta2] >> pg_dump -p testdb > /tmp/92dmp.dmp >> initdb --no-locale -E UTF8 >> pg_ctl start >> createdb testdb >> psql testdb -f /tmp/92dmp.dmp >> >> >> After all, the view v became updatable view. > I chatted about this on IRC for a bit. Apparently, updatability of > views is a mandatory feature in the sql standard and by relying on the > read-only-ness you were relying on non-standard behavior essentially. > I admit this is a pretty big pain (and I'm a real stickler for > backwards compatibility) but it's pretty hard to argue with the > standard. Workarounds are to revoke various privileges. Perhaps pg_dump from 9.3 should add REVOKE ALL ...; GRANT SELECT ...; when dumping views from older postgreSQL versions ? -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ -- 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] Regarding BGworkers
Robert Haas escribió: > On Mon, Aug 5, 2013 at 9:20 PM, Michael Paquier > wrote: > > On Fri, Aug 2, 2013 at 1:40 PM, Alvaro Herrera > > wrote: > >> That seems more mess than just keeping that function in postmaster.c. > >> I agree with moving the other one. > > Please find attached a patch for that can be applied on master branch. > > do_start_bgworker is renamed to StartBackgroundWorker and moved to > > bgworker.c. At the same time, bgworker_quickdie, bgworker_die and > > bgworker_sigusr1_handler are moved to bgworker.c as they are used in > > do_start_bgworker. > > This particular formulation doesn't seem quite good to me, because > we'd end up with a function called StartBackgroundWorker() and another > called StartOneBackgroundWorker() doing related but different things. > Maybe we can name things a bit better? Yeah, we also have start_bgworker(). I agree that we should rename things so that they make as much sense as possible. In the current code, we have this: StartOneBackgroundWorker() in postmaster.c start_bgworker() in postmaster.c do_start_bgworker() in postmaster.c With this patch we would have StartOneBackgroundWorker() in postmaster.c start_bgworker() in postmaster.c StartBackgroundWorker() in bgworker.c I think we should rename to something like this: maybe_start_bgworker() in postmaster.c do_start_bgworker() in postmaster.c StartBackgroundWorker() in bgworker.c (I would also rename the functions in 9.3 to avoid inconsistency). Not wedded to those particular names, but (1) I would add the "maybe" prefix because that's what that function does; and (2) it seems to me that stuff in bgworker.c tend to use CamelCaseNaming and postmaster.c uses names_with_stuffed_underscores. (My convention tends to be that "internal" stuff uses underscores while exposed APIs use CamelCase. I probably fail to do it really consistently.) -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] Foreground vacuum and buffer access strategy
On Mon, Aug 12, 2013 at 11:47 PM, Jeff Janes wrote: > Reviving a very old thread, because I've run into the issue again. > On Tue, May 29, 2012 at 11:58 AM, Robert Haas wrote: >> On Fri, May 25, 2012 at 4:06 PM, Jeff Janes wrote: >>> If I invoke vacuum manually and do so with VacuumCostDelay == 0, I >>> have basically declared my intentions to get this pain over with as >>> fast as possible even if it might interfere with other processes. >>> >>> Under that condition, shouldn't it use BAS_BULKWRITE rather than >>> BAS_VACUUM? The smaller ring size leads to a lot of synchronous WAL >>> flushes which I think can slow the vacuum down a lot. >> >> Of course, an autovacuum of a really big table could run too slowly, >> too, even though it's not a foreground task. > > True. But almost by definition, an autovacuum is not trying to run > inside a maintenance window. > > Would it be reasonable to upgrade the ring buffer size whenever > VacuumCostDelay is zero, regardless of whether it is a manual or an > auto vac? One thing I worry about is that many people may have > changed autovacuum_vacuum_cost_delay from 20 directly to 0 or -1, and > the accidental throttling on WAL syncs might be the only thing > preventing their system from falling over each time autovac of a large > table kicks in. I'm not sure what the right thing to do here is, but I definitely agree there's a problem. There are definitely cases where people want or indeed need to vacuum as fast as possible, and using a small ring buffer is not the way to do that. Now, tying that to VacuumCostDelay doesn't seem right, because setting that to 0 shouldn't suddenly change the behavior in other ways, as well. In general, the approach we've taken so far has been to try to hide the ring-buffer behavior from users and not make it tunable, but I'm not sure we can really get away with that in this case. Increasing the ring-buffer size has system-wide performance implications which could be very good (less bloat) or very bad (I/O starvation of concurrent activity). I don't think the system knows enough to guess which one will be better in any particular case. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] psql --single-transaction does not work as expected
On Tue, Aug 13, 2013 at 04:04:50PM +0200, Rafael Martinez wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Hello
>
> I want to report that psql --single-transaction does not work as one
> can expect after reading the help information for psql.
>
> psql --help says:
>
> - -1 ("one"), --single-transaction: execute command file as a single
> transaction
>
> If you run "psql -1 < sql_file.sql" insteed of "psql -1 -f
> sql_file.sql" the single-transaction parameter will not work.
This will be fixed in PG 9.3; from the release notes:
Allow the psql --single-transaction mode to work when
reading from standard input (Fabien Coelho, Robert Haas)
Previously this option only worked when reading from a file.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] psql --single-transaction does not work as expected
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hello
I want to report that psql --single-transaction does not work as one
can expect after reading the help information for psql.
psql --help says:
- -1 ("one"), --single-transaction: execute command file as a single
transaction
If you run "psql -1 < sql_file.sql" insteed of "psql -1 -f
sql_file.sql" the single-transaction parameter will not work.
Test case:
==
CREATE DATABASE test;
\c test
CREATE TABLE check_psql(id int, code text);
\q
# cat check_psql.sql
INSERT INTO check_psql (id,code) VALUES (1,'code1');
INSERT INTO check_psql (id,code) VALUES (2,code2);
# psql -1 test -f check_psql.sql
INSERT 0 1
psql:check_psql.sql:2: ERROR: column "code2" does not exist
LINE 1: INSERT INTO check_psql (id,code) VALUES (2,code2);
^
# psql -1 test -c "SELECT * FROM check_psql"
id | code
- +--
(0 rows)
# psql -1 test < /tmp/check_psql.sql
INSERT 0 1
ERROR: column "code2" does not exist
LINE 1: INSERT INTO check_psql (id,code) VALUES (2,code2);
^
#psql -1 test -c "SELECT * FROM check_psql"
id | code
- +---
1 | code1
(1 row)
The docs for psql at
http://www.postgresql.org/docs/current/static/app-psql.html
have the right information, " -1 --single-transaction
When psql executes a script with the -f option "
I think we should either update the psql --help information for
- --single-transaction and say that this parameter only works together
with -f or update the psql code so psql -1 < file.sql also works.
regards,
- --
Rafael Martinez Guerrero
Center for Information Technology
University of Oslo, Norway
PGP Public Key: http://folk.uio.no/rafael/
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.14 (GNU/Linux)
iEYEARECAAYFAlIKPQEACgkQBhuKQurGihQaGwCggvy+Fgiw1TlseZKM8oq4U/na
cgQAnRN4sw9NHBajG57wL0P+08p6Nb3y
=cNHB
-END PGP SIGNATURE-
--
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] How to create read-only view on 9.3
On Tue, Aug 13, 2013 at 5:37 AM, Tomonari Katsumata wrote: > Hi Szymon, > > Thank you for response. > > >>> Could you show an example? >> > I do below things on one server. > The path to database cluster and port are > different with each other. > > [9.2.4] > initdb --no-locale -E UTF8 > pg_ctl start > createdb testdb > psql testdb -c "create table tbl(i int)" > psql testdb -c "insert into tbl values (generate_series(1,10))" > psql testdb -c "create view v as select * from tbl" > > [9.3beta2] > pg_dump -p testdb > /tmp/92dmp.dmp > initdb --no-locale -E UTF8 > pg_ctl start > createdb testdb > psql testdb -f /tmp/92dmp.dmp > > > After all, the view v became updatable view. I chatted about this on IRC for a bit. Apparently, updatability of views is a mandatory feature in the sql standard and by relying on the read-only-ness you were relying on non-standard behavior essentially. I admit this is a pretty big pain (and I'm a real stickler for backwards compatibility) but it's pretty hard to argue with the standard. Workarounds are to revoke various privileges. merlin -- 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] timeline signedness
On Wed, 2013-08-07 at 21:55 -0400, Peter Eisentraut wrote:
> WAL timelines are unsigned 32-bit integers everywhere, except the
> replication parser (replication/repl_gram.y and
> replication/repl_scanner.l) treats them as signed 32-bit integers. It's
> obviously a corner case, but it would be prudent to be correct about
> this. It should be easy to fix in those grammar files.
Here is a patch to fix this.
diff --git a/src/backend/replication/repl_gram.y b/src/backend/replication/repl_gram.y
index bce18b8..f465530 100644
--- a/src/backend/replication/repl_gram.y
+++ b/src/backend/replication/repl_gram.y
@@ -56,7 +56,7 @@ Node *replication_parse_result;
%union {
char *str;
bool boolval;
- int32 intval;
+ uint32 uintval;
XLogRecPtrrecptr;
Node *node;
@@ -66,7 +66,7 @@ Node *replication_parse_result;
/* Non-keyword tokens */
%token SCONST
-%token ICONST
+%token UCONST
%token RECPTR
/* Keyword tokens. */
@@ -85,7 +85,7 @@ Node *replication_parse_result;
%type base_backup start_replication identify_system timeline_history
%type base_backup_opt_list
%type base_backup_opt
-%type opt_timeline
+%type opt_timeline
%%
firstcmd: command opt_semicolon
@@ -175,14 +175,7 @@ start_replication:
;
opt_timeline:
- K_TIMELINE ICONST
-{
- if ($2 <= 0)
- ereport(ERROR,
-(errcode(ERRCODE_SYNTAX_ERROR),
- (errmsg("invalid timeline %d", $2;
- $$ = $2;
-}
+ K_TIMELINE UCONST { $$ = $2; }
| /* nothing */ { $$ = 0; }
;
@@ -190,15 +183,10 @@ opt_timeline:
* TIMELINE_HISTORY %d
*/
timeline_history:
- K_TIMELINE_HISTORY ICONST
+ K_TIMELINE_HISTORY UCONST
{
TimeLineHistoryCmd *cmd;
- if ($2 <= 0)
- ereport(ERROR,
-(errcode(ERRCODE_SYNTAX_ERROR),
- (errmsg("invalid timeline %d", $2;
-
cmd = makeNode(TimeLineHistoryCmd);
cmd->timeline = $2;
diff --git a/src/backend/replication/repl_scanner.l b/src/backend/replication/repl_scanner.l
index b4743e6..3d930f1 100644
--- a/src/backend/replication/repl_scanner.l
+++ b/src/backend/replication/repl_scanner.l
@@ -83,8 +83,8 @@ TIMELINE_HISTORY { return K_TIMELINE_HISTORY; }
" ";
{digit}+ {
- yylval.intval = pg_atoi(yytext, sizeof(int32), 0);
- return ICONST;
+ yylval.uintval = strtoul(yytext, NULL, 10);
+ return UCONST;
}
{hexdigit}+\/{hexdigit}+ {
--
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] Regarding BGworkers
On Mon, Aug 5, 2013 at 9:20 PM, Michael Paquier wrote: > On Fri, Aug 2, 2013 at 1:40 PM, Alvaro Herrera > wrote: >> That seems more mess than just keeping that function in postmaster.c. >> I agree with moving the other one. > Please find attached a patch for that can be applied on master branch. > do_start_bgworker is renamed to StartBackgroundWorker and moved to > bgworker.c. At the same time, bgworker_quickdie, bgworker_die and > bgworker_sigusr1_handler are moved to bgworker.c as they are used in > do_start_bgworker. This particular formulation doesn't seem quite good to me, because we'd end up with a function called StartBackgroundWorker() and another called StartOneBackgroundWorker() doing related but different things. Maybe we can name things a bit better? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] How to create read-only view on 9.3
Hi Szymon, Thank you for response. >> Could you show an example? > I do below things on one server. The path to database cluster and port are different with each other. [9.2.4] initdb --no-locale -E UTF8 pg_ctl start createdb testdb psql testdb -c "create table tbl(i int)" psql testdb -c "insert into tbl values (generate_series(1,10))" psql testdb -c "create view v as select * from tbl" [9.3beta2] pg_dump -p testdb > /tmp/92dmp.dmp initdb --no-locale -E UTF8 pg_ctl start createdb testdb psql testdb -f /tmp/92dmp.dmp After all, the view v became updatable view. --- $ psql testdb psql (9.3beta2) Type "help" for help. testdb=# select * from v; i 1 2 3 4 5 6 7 8 9 10 (10 rows) testdb=# insert into v values (11); INSERT 0 1 testdb=# select * from v; i 1 2 3 4 5 6 7 8 9 10 11 (11 rows) regards, NTT Software Corporation Tomonari Katsumata (2013/08/13 19:16), Szymon Guz wrote: > On 13 August 2013 11:43, Tomonari Katsumata < > katsumata.tomon...@po.ntts.co.jp> wrote: > >> Hi, >> >> Could anyone tell me how to create read-only view on >> PostgreSQL 9.3 ? >> >> I've been testing updatable views and noticed that >> all simple views are updatable. >> >> When I use pg_dump for upgrading from PostgreSQL 9.2 >> to PostgreSQL 9.3 and if the databse has views, >> all views are updatable on the restored database. >> >> I want to make these views read-only like PostgreSQL9.2. >> How can I do this? Should I make access control on users ? >> (Sorry, I couldn't find any explanations on document.) >> >> regards, >> >> NTT Software Corporation >> Tomonari Katsumata >> >> >> >> Could you show an example? > > Szymon > -- 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] How to create read-only view on 9.3
On 13 August 2013 11:43, Tomonari Katsumata < katsumata.tomon...@po.ntts.co.jp> wrote: > Hi, > > Could anyone tell me how to create read-only view on > PostgreSQL 9.3 ? > > I've been testing updatable views and noticed that > all simple views are updatable. > > When I use pg_dump for upgrading from PostgreSQL 9.2 > to PostgreSQL 9.3 and if the databse has views, > all views are updatable on the restored database. > > I want to make these views read-only like PostgreSQL9.2. > How can I do this? Should I make access control on users ? > (Sorry, I couldn't find any explanations on document.) > > regards, > > NTT Software Corporation > Tomonari Katsumata > > > > Could you show an example? Szymon
[HACKERS] How to create read-only view on 9.3
Hi, Could anyone tell me how to create read-only view on PostgreSQL 9.3 ? I've been testing updatable views and noticed that all simple views are updatable. When I use pg_dump for upgrading from PostgreSQL 9.2 to PostgreSQL 9.3 and if the databse has views, all views are updatable on the restored database. I want to make these views read-only like PostgreSQL9.2. How can I do this? Should I make access control on users ? (Sorry, I couldn't find any explanations on document.) regards, NTT Software Corporation Tomonari Katsumata -- 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] 9.3 release notes suggestions
> Thanks for the many suggestions on improving the 9.3 release notes. > There were many ideas I would have never thought of. Please keep the suggestions > coming. One small suggestion: Allow foreign data wrappers to support writes (inserts/updates/deletes) on foreign tables (KaiGai Kohei) This is the in-core functionality, so ISTM it would be better that this is stated in the section of Object Manipulation rather than in that of Additional Modules. Please find attached a patch. Thanks, Best regards, Etsuro Fujita REL9_3_BETA2_release.patch Description: Binary data -- 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] updatable/deletable terminology
On 13 August 2013 00:01, Peter Eisentraut wrote:
> On Wed, 2013-08-07 at 21:19 -0400, Peter Eisentraut wrote:
>> To make the view updatable, provide an unconditional ON DELETE DO
>> INSTEAD rule or an INSTEAD OF DELETE trigger.
>>
>> I think it's a bit strange to claim that adding a DELETE rule/trigger
>> makes a view *updatable*. I suspect someone thought they would apply
>> the term "updatable" in an SQL standard sense, but that seems
>> backwards,
>> because you get to these error conditions exactly because the view as
>> defined was not Updatable(tm).
>
> After some consideration, I think the best fix here is to revert to the
> 9.2 wording
>
> "You need an unconditional ON DELETE DO INSTEAD rule or an INSTEAD OF
> DELETE trigger."
>
That's how I had it in the patch I submitted, but perhaps it should be
"You need an INSTEAD OF DELETE trigger or an unconditional ON DELETE
DO INSTEAD rule."
to reflect the fact that the docs now recommend triggers ahead of rules.
> The addition of the term "updatable" was simply wrong here.
>
In the docs we're using "udpatable" as a generic term meaning support
for INSERT, UPDATE and DELETE, and we're not using the terms
"insertable" or "deletable". Also the error detail that immediately
precedes this hint uses the term "updatable". For example:
CREATE VIEW one AS SELECT 1 AS val;
INSERT INTO one VALUES (1);
ERROR: cannot insert into view "one"
DETAIL: Views that do not select from a single table or view are not
automatically updatable.
HINT: To make the view insertable, provide an unconditional ON INSERT
DO INSTEAD rule or an INSTEAD OF INSERT trigger.
so if there is a problem there, it's in the mix of terminology between
the detail and the hint ("updatable" vs "insertable"). But at least in
this case the hint is technically correct -- adding such a rule or
trigger would make the view insertable.
In the UPDATE and DELETE cases, following the hint's suggestion and
adding just an UPDATE rule or trigger, or just a DELETE rule or
trigger, wouldn't actually make the view updatable according to our
current interpretation of the spec, which would require both.
So on balance I think you're right, and it would be better to simply say:
ERROR: cannot insert into view "one"
DETAIL: Views that do not select from a single table or view are not
automatically updatable.
HINT: You need an INSTEAD OF INSERT trigger or an unconditional ON
INSERT DO INSTEAD rule.
Regards,
Dean
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
