On Fri, Mar 27, 2015 at 4:18 PM, Anil Menon wrote:
> Hi,
>
> I am trying to wrap my head around a strange problem I am having. I have
> double checked the documentation but I could not find anything on this.
>
> [...]
>
> However I get no rows returned from the select statement- looks the
>
Hi,
I am trying to wrap my head around a strange problem I am having. I have
double checked the documentation but I could not find anything on this.
I am attaching a simplified version of my problem. I my TEST 4 I expect 1
row but I get nothing. The test is
with I(id) as (
insert into abc(colD
Hi Sérgio:
On Sun, Dec 7, 2014 at 9:11 PM, Sérgio Saquetim
wrote:
> I've noticed a strange behavior in the generate_series functions.
>
> I'm trying to get all days between a start and an end date including the
> bounds. So naturally I've tried something like the query below
> .
>
As both your
On Sun, Dec 07, 2014 at 08:25:48PM -0200, Sérgio Saquetim wrote:
>
> I wasn't paying attention to the fact that generate_series really expects
> for timezone inputs. So when I was passing the upper bound
> as '2014-10-20'::DATE, the value was being cast to 2014-10-20 00:00:00-02.
>
> postgres=# S
You've nailed it, thank you!
Finally I'm understanding what's going on.
I wasn't paying attention to the fact that generate_series really expects
for timezone inputs. So when I was passing the upper bound
as '2014-10-20'::DATE, the value was being cast to 2014-10-20 00:00:00-02.
postgres=# SELEC
On 12/07/2014 12:11 PM, Sérgio Saquetim wrote:
I've noticed a strange behavior in the generate_series functions.
I'm trying to get all days between a start and an end date including the
bounds. So naturally I've tried something like the query below
.
The real query uses generate_series to join
On 12/07/2014 12:11 PM, Sérgio Saquetim wrote:
I've noticed a strange behavior in the generate_series functions.
I'm trying to get all days between a start and an end date including the
bounds. So naturally I've tried something like the query below
.
The real query uses generate_series to join
I've noticed a strange behavior in the generate_series functions.
I'm trying to get all days between a start and an end date including the
bounds. So naturally I've tried something like the query below
.
The real query uses generate_series to join other tables and is much more
complicated, but f
I apologize for my carelessness. Error rollback code including all
completed "revoke". Therefore, users can create functions. If you add
another commit before "grant temp on schema public to sec_privilege;" it
will be seen that create a function is also not possible.
14
15 create database
Hello!
Please help to understand why the line 35 ("grant temp on schema public
to sec_privilege") generates an error "ERROR: invalid privilege type
TEMP for schema" and successfully created function "readonly" at the end
of listing, but if it is removed, the function in lines 45-49 will not
Tom Lane wrote:
> The SQL standard uses "=" for assignment in other contexts,
> most notably UPDATE, but also the SQL/PSM standard uses it in
> which is the exact same thing as in
> pl/pgsql. So while purists might wish we only accepted :=, doing
> so would be inconsistent with SQL.
>
> I think
David Johnston writes:
> A bogus warning is nearly as bad as simply disallowing the syntax in the
> first place and I do not like turning one on unless there is the decision to
> disallow the syntax in the future.
TBH I do not see this happening. GET DIAGNOSTICS is just the tip of the
iceberg.
Chris Travers-5 wrote
> My preference would be that at some point we start adding warnings when =
> is used as an assignment. Such warnings could be turned off. Then at
> some
> later point we can decide whether to change the behavior. A decision to
> changing the language would be different if
>
> A comment was made that "GET DIAGNOSTICS var = item;" is standard defined.
> Is the use of ":=" for assignment also standard defined? If so its not that
> inconsistent standards surprise me but...anyway.
":=" coming from different world (ALGOL like languages) and is never
used in SQL. Oracle
On Sat, Jun 1, 2013 at 2:52 AM, David Johnston wrote:
> Chris Travers-5 wrote
> > However = as assignment is particularly odd to me for two reasons. First
> > it is not ambiguous but it leads to difficult to read constructs, like
> > this:
> >
> >out_var = in_left = in_right;
>
> Agreed but
Chris Travers-5 wrote
> However = as assignment is particularly odd to me for two reasons. First
> it is not ambiguous but it leads to difficult to read constructs, like
> this:
>
>out_var = in_left = in_right;
Agreed but the genie is already out of the bottle and I am OK with something
at t
2013/6/1 Chris Travers :
> Agreed about undocumented behavior (actually there is a *lot* of
> undocumented behavior in PostgreSQL as I have slowly found out-- if you want
> to see a lot of it, go look at the pg_dump source code).
>
> However = as assignment is particularly odd to me for two reasons
Agreed about undocumented behavior (actually there is a *lot* of
undocumented behavior in PostgreSQL as I have slowly found out-- if you
want to see a lot of it, go look at the pg_dump source code).
However = as assignment is particularly odd to me for two reasons. First
it is not ambiguous but i
2013/6/1 David Johnston :
> Tom Lane-2 wrote
>> Stephen Frost <
>
>> sfrost@
>
>> > writes:
>>> * Moshe Jacobson (
>
>> moshe@
>
>> ) wrote:
Any PG committers who can change this in 9.3?
>>
>>> It will certainly not be changed for 9.3.
>>
>> IMO, if we do anything about this at all, it should
Tom Lane-2 wrote
> Stephen Frost <
> sfrost@
> > writes:
>> * Moshe Jacobson (
> moshe@
> ) wrote:
>>> Any PG committers who can change this in 9.3?
>
>> It will certainly not be changed for 9.3.
>
> IMO, if we do anything about this at all, it should be to document the
> "=" option not remov
2013/6/1 Tom Lane :
> Stephen Frost writes:
>> * Moshe Jacobson (mo...@neadwerx.com) wrote:
>>> Any PG committers who can change this in 9.3?
>
>> It will certainly not be changed for 9.3.
>
> IMO, if we do anything about this at all, it should be to document the
> "=" option not remove it. If we
Stephen Frost writes:
> * Moshe Jacobson (mo...@neadwerx.com) wrote:
>> Any PG committers who can change this in 9.3?
> It will certainly not be changed for 9.3.
IMO, if we do anything about this at all, it should be to document the
"=" option not remove it. If we change it, the squawks from pe
2013/5/28 Steve Crawford :
> On 05/28/2013 01:06 PM, Stephen Frost wrote:
>>
>> * Moshe Jacobson (mo...@neadwerx.com) wrote:
>>>
>>> It seems that the comparison operator "=" is functioning as the
>>> assignment
>>> operator ":=" in this plpgsql trigger script I wrote. I was under the
>>> impressio
Hello
2013/5/28 Moshe Jacobson :
> On Tue, May 28, 2013 at 4:06 PM, Stephen Frost wrote:
>>
>> Both are supported. It's not really documented as using '=' is
>> considered 'legacy' but it's also extensively used and removing it would
>> break quite a bit of code for people.
>
>
> This is crazy!
On 05/28/2013 01:06 PM, Stephen Frost wrote:
* Moshe Jacobson (mo...@neadwerx.com) wrote:
It seems that the comparison operator "=" is functioning as the assignment
operator ":=" in this plpgsql trigger script I wrote. I was under the
impression that "=" is only for comparison and not assignment
* Moshe Jacobson (mo...@neadwerx.com) wrote:
> Any PG committers who can change this in 9.3?
It will certainly not be changed for 9.3.
As suggested, perhaps in 10.0, but I tend to doubt it. It will
certainly be mentioned in the release notes when it happens.
Thanks,
Ste
On Tue, May 28, 2013 at 4:06 PM, Stephen Frost wrote:
> Both are supported. It's not really documented as using '=' is
> considered 'legacy' but it's also extensively used and removing it would
> break quite a bit of code for people.
>
This is crazy! By leaving it in, they are allowing my obsol
2013/5/28 Stephen Frost :
> * Moshe Jacobson (mo...@neadwerx.com) wrote:
>> It seems that the comparison operator "=" is functioning as the assignment
>> operator ":=" in this plpgsql trigger script I wrote. I was under the
>> impression that "=" is only for comparison and not assignment. If this i
* Moshe Jacobson (mo...@neadwerx.com) wrote:
> It seems that the comparison operator "=" is functioning as the assignment
> operator ":=" in this plpgsql trigger script I wrote. I was under the
> impression that "=" is only for comparison and not assignment. If this is
> true, please explain the tr
Dear PostgreSQL gurus,
It seems that the comparison operator "=" is functioning as the assignment
operator ":=" in this plpgsql trigger script I wrote. I was under the
impression that "=" is only for comparison and not assignment. If this is
true, please explain the transcript below. If it's not t
Chris Travers wrote:
> I have found recently that tables in certain contexts seem to have a
> name pseudocolumn. I was wondering if there is any documentation as
> to what this is and what it signifies.
>
> postgres=# CREATE table TEST2 (a text, b text);
> CREATE TABLE
> postgres=# INSERT INTO te
See documentation, chapter Viii.E.2.2.2
2011/11/11, Chris Travers :
> Hi;
>
> I have found recently that tables in certain contexts seem to have a
> name pseudocolumn. I was wondering if there is any documentation as
> to what this is and what it signifies.
>
> postgres=# CREATE table TEST2 (a te
Chris Travers writes:
> I have found recently that tables in certain contexts seem to have a
> name pseudocolumn. I was wondering if there is any documentation as
> to what this is and what it signifies.
I/O conversion cast from composite type to string. You might find
this 9.1 patch informativ
Hi;
I have found recently that tables in certain contexts seem to have a
name pseudocolumn. I was wondering if there is any documentation as
to what this is and what it signifies.
postgres=# CREATE table TEST2 (a text, b text);
CREATE TABLE
postgres=# INSERT INTO test2 values ('', '');
I
On Sat, Apr 4, 2009 at 10:07 AM, Justin wrote:
> I think i may be the way the function is being called??
>
> if you are doing Select fnvs.docrelatedassociatedetails()
>
> it will not return any records, it needs to be
>
> Select * From fnvs.docrelatedassociatedetails()
select func();
will return
I think i may be the way the
function is being called??
if you are doing Select fnvs.docrelatedassociatedetails()
it will not return any records, it needs to be
Select * From
fnvs.docrelatedassociatedetails()
c k wrote:
Hi all,
I am facing a small but strange problem when using a plpgsql f
Hi all,
I am facing a small but strange problem when using a plpgsql function as
below.
CREATE OR REPLACE FUNCTION fnvs.docrelatedassociatedetails(p_docid integer,
p_addtype smallint, p_associateid integer, OUT docid integer, OUT
associateid integer, OUT addressline1 varchar,OUT addressline2 varch
Phoenix Kiula writes:
> I guess my question is, how should I remove all pending locks on a
> table so that I can get on with the rest of the stuff?
>
> I mean, even if I can now find an offending RULE on the table, I
> cannot replace or remove it. '
You're off on the wrong track. Locks are held
phoenix.ki...@gmail.com (Phoenix Kiula) writes:
> On Wed, Mar 4, 2009 at 12:55 AM, Phoenix Kiula
> wrote:
>> I guess my question is, how should I remove all pending locks on a
>> table so that I can get on with the rest of the stuff?
>>
>> I mean, even if I can now find an offending RULE on the t
On Wed, Mar 4, 2009 at 1:23 AM, Phoenix Kiula wrote:
> On Wed, Mar 4, 2009 at 1:17 AM, Tom Lane wrote:
>> Phoenix Kiula writes:
>>> How can I get rid of these open locks?
>>
>> Close the transactions that are holding them. Look into
>> pg_stat_activity and pg_prepared_xacts.
>
>
> Thanks for th
On Wed, Mar 4, 2009 at 1:17 AM, Tom Lane wrote:
> Phoenix Kiula writes:
>> How can I get rid of these open locks?
>
> Close the transactions that are holding them. Look into
> pg_stat_activity and pg_prepared_xacts.
Thanks for this. But can I simply delete all the pg_locks table? Or
delete all
On Wed, Mar 4, 2009 at 12:55 AM, Phoenix Kiula wrote:
> I guess my question is, how should I remove all pending locks on a
> table so that I can get on with the rest of the stuff?
>
> I mean, even if I can now find an offending RULE on the table, I
> cannot replace or remove it. '
Any ideas? I
Phoenix Kiula writes:
> How can I get rid of these open locks?
Close the transactions that are holding them. Look into
pg_stat_activity and pg_prepared_xacts.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to you
I guess my question is, how should I remove all pending locks on a
table so that I can get on with the rest of the stuff?
I mean, even if I can now find an offending RULE on the table, I
cannot replace or remove it. '
Thanks for any pointers!
--
Sent via pgsql-general mailing list (pgsql-genera
- "Phoenix Kiula" wrote:
> On Wed, Mar 4, 2009 at 12:36 AM, Adrian Klaver
> wrote:
> >
> > Are you connected to the right database?. I have been in that
> situation, looking at the log for db A and doing things in db B.
>
>
> Thanks. I only have one database, so yes I am connected to it.
On Wed, Mar 4, 2009 at 12:36 AM, Adrian Klaver wrote:
>
> Are you connected to the right database?. I have been in that situation,
> looking at the log for db A and doing things in db B.
Thanks. I only have one database, so yes I am connected to it.
I have the lock file in /tmp:.s.PGSQL.54
- "Phoenix Kiula" wrote:
> > commit the transaction where you altered the table. It has an open
> lock on
> > the table.
>
>
>
> =# commit;
>
> WARNING: there is no transaction in progress
> COMMIT
> Time: 0.282 ms
>
>
> So no, there's nothing pending.
>
> --
Are you connected to t
Although when I try this:
select pg_class.relname,pg_locks.* from pg_class,pg_locks where
pg_class.relfilenode=pg_locks.relation;
There are many rows!
How can I get rid of these open locks?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscripti
> commit the transaction where you altered the table. It has an open lock on
> the table.
=# commit;
WARNING: there is no transaction in progress
COMMIT
Time: 0.282 ms
So no, there's nothing pending.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to y
On Wed, Mar 4, 2009 at 12:10 AM, Tom Lane wrote:
> Phoenix Kiula writes:
>> Now when I do:
>> vacuum analyze TABLENAME
>> or
>> delete from TABLENAME where id = 99
>> Nothing happens! The carriage return means the my shell cursor goes to
>> the next line, but it just stays there.
>
> Did you
On Tuesday 03 March 2009, Phoenix Kiula wrote:
> HI. I made a small alteration to a table (added a column).
>
> Now when I do:
>
> vacuum analyze TABLENAME
>
> or
>
> delete from TABLENAME where id = 99
>
> Nothing happens! The carriage return means the my shell cursor goes to
> the next line,
Phoenix Kiula writes:
> Now when I do:
> vacuum analyze TABLENAME
> or
> delete from TABLENAME where id = 99
> Nothing happens! The carriage return means the my shell cursor goes to
> the next line, but it just stays there.
Did you forget the semicolon?
regards, tom l
HI. I made a small alteration to a table (added a column).
Now when I do:
vacuum analyze TABLENAME
or
delete from TABLENAME where id = 99
Nothing happens! The carriage return means the my shell cursor goes to
the next line, but it just stays there. I thought something may be
happening sile
On Mon, 2007-10-01 at 23:19 +0600, Nurlan Mukhanov wrote:
> There is a table with unique rows. But before insert trigger checks
> data and returns NULL if such record exist and NEW if not.
>
> But from time to time I'm getting an error in my log file
>
> faled query: INSERT INTO viewed_members (m
There is a table with unique rows. But before insert trigger checks
data and returns NULL if such record exist and NEW if not.
But from time to time I'm getting an error in my log file
faled query: INSERT INTO viewed_members (member_id, viewed_id) VALUES
('93701','41719')
context: ERROR: duplicat
Thanks for the response Ragnar. I would have expected this query to
fail, since the sub-query doesn't work by itself:
SELECT foo_field FROM foo WHERE foo_field IN (SELECT foo_field FROM par);
But it obviously doesn't. So does that subselect implicitly read as:
IN (SELECT foo_field FROM par
On þri, 2006-10-17 at 15:58 -0700, Ken Tanzer wrote:
> We're a little puzzled by this (apparently) strange behavior, and would
> be curious to know what you folks make of it. Thanks.
not sure exactly what you are referring to, but:
(rearranged quotes to group output with SQL)
> SELECT foo_field
We're a little puzzled by this (apparently) strange behavior, and would
be curious to know what you folks make of it. Thanks.
Ken
CREATE TABLE foo (
foo_field integer );
CREATE TABLE par(
par_field integer );
SELECT VERSION();
SELECT foo_field FROM par;
SELECT foo_field FROM foo
On Thu, Jun 29, 2006 at 14:27:30 +0200,
Martijn van Oosterhout wrote:
> On Thu, Jun 29, 2006 at 01:21:19PM +0100, Simon Riggs wrote:
> > The issue is the difference between start of transaction and time when
> > the serializable snapshot is taken. Since BEGIN and other commands may
> > be issued
"Tom Lane" <[EMAIL PROTECTED]> wrote
>
> It can, but there are cases where you want the lock to be taken before
> the snapshot is set. Otherwise, there could be committed changes in the
> database that you can't see in your snapshot. I think there are some
> examples in the manual, or check the
"Qingqing Zhou" <[EMAIL PROTECTED]> writes:
> "Tom Lane" <[EMAIL PROTECTED]> wrote
>> Right, the snapshot does not become set until you do a non-utility
>> command (normally, SELECT/INSERT/UPDATE/DELETE). This is a feature, not
>> a bug, because it lets the transaction take table locks before its
"Tom Lane" <[EMAIL PROTECTED]> wrote
>
> Right, the snapshot does not become set until you do a non-utility
> command (normally, SELECT/INSERT/UPDATE/DELETE). This is a feature, not
> a bug, because it lets the transaction take table locks before its
> snapshot becomes set.
>
Hm, mostly I unders
On Thu, 2006-06-29 at 14:27 +0200, Martijn van Oosterhout wrote:
> On Thu, Jun 29, 2006 at 01:21:19PM +0100, Simon Riggs wrote:
> > The issue is the difference between start of transaction and time when
> > the serializable snapshot is taken. Since BEGIN and other commands may
> > be issued as sepa
On Thu, Jun 29, 2006 at 01:21:19PM +0100, Simon Riggs wrote:
> The issue is the difference between start of transaction and time when
> the serializable snapshot is taken. Since BEGIN and other commands may
> be issued as separate network requests it makes sense to defer taking
> the snapshot until
On Wed, 2006-06-28 at 21:20 +0200, Martijn van Oosterhout wrote:
> On Wed, Jun 28, 2006 at 02:48:01PM -0400, Brad Nicholson wrote:
> > I'm seeing something fairly unintuitive about serializable transactions.
> >
> > Taking the following test case:
>
>
>
> > http://www.postgresql.org/docs/8.1/in
Martijn van Oosterhout writes:
> I think the issue here is that transaction begin is not when you type
> "begin" but at your first actual query. You can obviously only start a
> transaction once you know what serialisation level you want, and you
> don't see that till after the begin.
Right, the
On Wed, Jun 28, 2006 at 14:48:01 -0400,
Brad Nicholson <[EMAIL PROTECTED]> wrote:
> I'm seeing something fairly unintuitive about serializable transactions.
>
> "When a transaction is on the serializable level, a SELECT query sees
> only data committed before the transaction began; it never sees
On Wed, Jun 28, 2006 at 02:48:01PM -0400, Brad Nicholson wrote:
> I'm seeing something fairly unintuitive about serializable transactions.
>
> Taking the following test case:
> http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html
>
> "When a transaction is on the serializable le
I'm seeing something fairly unintuitive about serializable transactions.
Taking the following test case:
CREATE TABLE foo (id integer);
t1 t2
-- BEGIN;
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO foo (id) --
V
On Fri, Jan 06, 2006 at 03:26:27PM -0200, Bruno Almeida do Lago wrote:
> #!/bin/bash
>
> imprime () {
> echo `date +"%d/%m/%y %H:%M:%S |"` $*
> }
>
> BANCOS=`psql -Atl | cut -d"|" -f1 | grep -v template`
> for BANCO in $BANCOS; do
> imprime "Inicio do backup da base $BANCO"
> done
>
>
> [E
Hello my friends!
I was making a pretty simple script to export our databases, and found a
strange (funny) behavior of psql (or bash?).
[EMAIL PROTECTED] psql -Atl | cut -d"|" -f1 | grep -v template
Db_1
Db_2
Db_3
Db_4
The databases name were changed due privacy reasons (our client database),
bu
Csaba Nagy <[EMAIL PROTECTED]> writes:
> Ok, I found a fix which works for me: don't use ON COMMIT DELETE ROWS on
> the temporary tables, but explicitly delete the rows once processed.
> However, I think it should work with ON COMMIT DELETE ROWS too, and it
> works fine indeed in 8.0.3.
I found th
Csaba Nagy <[EMAIL PROTECTED]> writes:
> I have observed a strange behavior on 8.1 of an insert statement into a
> temporary table done from a delete trigger.
In an assert-enabled build this dumps core, so I'd say you've found a
bug ...
regards, tom lane
-
Ok, I found a fix which works for me: don't use ON COMMIT DELETE ROWS on
the temporary tables, but explicitly delete the rows once processed.
However, I think it should work with ON COMMIT DELETE ROWS too, and it
works fine indeed in 8.0.3.
Cheers,
Csaba.
On Thu, 2005-11-17 at 16:22, Csaba Nagy w
Hi all,
I have observed a strange behavior on 8.1 of an insert statement into a
temporary table done from a delete trigger.
I've attached a test case.
Observe that the NOTICE saying the rows were inserted occurs all 5 times
for the 8.0.3 server and only for the first 2 times for the 8.1
installati
75 matches
Mail list logo
