Setting up tablepace

Hi,

What happens to cursors when new data is added to a table after you
start iterating
over its rows?

For example, given the following loop...

for rule in select tc.sid, tc.s, td.rule, td.returns
from tcell tc
inner join tcelldef td on (tc.p = td.p)
where tc.iasid = current_audit_sid()
or committed_sid in ( select committed
from tcellread tcr
where tc.sid = tcr.tcell )
for update of tc loop
...
end loop;

some code in the loop might add a record into tcellread that causes the where
condition to become true for a row in which it was previously false.
Will the cursor
eventually see it?

Thanks,
Andy

Hi all,
I have a problem with setting up tablespace on a ramdisk.
the ramdisk shall be used temporary for storing unprotected map data, that is normaly protected.

I'm working in Linux.
I have an ordirnary Linux user called normaluser, a user called chartuser (the ramdisk owner, the password is not known to the normal users), and a ordinary postgres user.

Chartuser and root are the only ones that has access to the ramdisk.

I have succeeded to store (as normaluser) the unprotected map files on the ramdisk, only accessable for chartuser.

I would now like to import the map files to a GIS db that has the tabespace on the ramdisk. But I'm not able to create the tablespace on the ramdisk.
Trying logged in as chartuser to run: psql -c "CREATE TABLESPACE ramspc LOCATION '/tmp/ramdisk0/tblspace'"
"could not set permission on directory "/tmp/ramdisk0/tblspace": premission denied"...

Any ideas, anyone?
Thanks,
Paul

On Tue, Jun 14, 2011 at 11:54 PM, Andy Chambers <achambers(at)mcna(dot)net> wrote:
> Hi,
>
> What happens to cursors when new data is added to a table after you
> start iterating
> over its rows?
>
> For example, given the following loop...
>
> for rule in select tc.sid, tc.s, td.rule, td.returns
>                     from tcell tc
>               inner join tcelldef td on (tc.p = td.p)
>                    where tc.iasid = current_audit_sid()
>                       or committed_sid in ( select committed
>                                               from tcellread tcr
>                                              where tc.sid = tcr.tcell )
>                         for update of tc loop
>  ...
> end loop;
>
> some code in the loop might add a record into tcellread that causes the where
> condition to become true for a row in which it was previously false.
> Will the cursor
> eventually see it?

nope!

merlin

Cursors only see the data that is the effect of the query. That output
doesn't get updated. It would actually be pretty bad if that was the
case.

On 06/15/11 3:34 AM, Malm Paul wrote:
> Hi all,
> I have a problem with setting up tablespace on a ramdisk.
> the ramdisk shall be used temporary for storing unprotected map data, that is normaly protected.
>
> I'm working in Linux.
> I have an ordirnary Linux user called normaluser, a user called chartuser (the ramdisk owner, the password is not known to the normal users), and a ordinary postgres user.
>
> Chartuser and root are the only ones that has access to the ramdisk.
>
> I have succeeded to store (as normaluser) the unprotected map files on the ramdisk, only accessable for chartuser.
>
> I would now like to import the map files to a GIS db that has the tabespace on the ramdisk. But I'm not able to create the tablespace on the ramdisk.
> Trying logged in as chartuser to run: psql -c "CREATE TABLESPACE ramspc LOCATION '/tmp/ramdisk0/tblspace'"
> "could not set permission on directory "/tmp/ramdisk0/tblspace": premission denied"...

I hope you drop this tablespace and everything in it before
rebooting... and that your system never reboots unexpectedly....

that said, its the postgres server process that needs write access to
create the tablespace. I'd create a directory in the ramdisk called
pgsql or something, chown postgres && chmod 700, then put your
tablespace in that.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast