Gary M wrote:
> Vick,
>
> I would love to use ZFS, this project requires RHEL/SeLinux MLS. Without
> MLS and RH support, ZFS is a no-go.
btrfs could be an option. it does mostly the same things as zfs.
> On Fri, Mar 2, 2018 at 2:34 PM, Vick Khera wrote:
>
> > On Fri, Mar 2, 2018 at 2:34 PM, G
other copies of
essentially the same database.
And all the other stored functions were loaded fine. It's just this one that
went wrong.
Thanks in advance for any insights you can share.
cheers,
raf
Tom Lane wrote:
> r...@raf.org writes:
> > I have a stored function with code that looks like:
>
> > raise notice '% %', 'blah_history.original_id', r;
>
> > But I'm getting this compilation error when it tries to load this
> > function:
>
> > ERROR: too many parameters specifi
David G. Johnston wrote:
> On Wed, Apr 18, 2018 at 7:04 PM, Melvin Davidson
> wrote:
>
> >
> > Normally, literals are inside the first quotes. IE: raise notice '
> > blah_history.original_id' %', r;
> >
>
> But a compiler doesn't care about human concepts like "normally" - it just
> cares abou
Adrian Klaver wrote:
> On 04/18/2018 06:02 PM, r...@raf.org wrote:
> > Hi,
> >
> > postgresql-9.5.12 on debian-9
> >
> > I have a stored function with code that looks like:
> >
> > create or replace function tla_audit_delete_thing()
> > returns boolean stable language plpgsql as $$
>
ir reasons.
i've encountered other new behaviour with pygresql-5+ that i had
to find ways to disable/revert so it's not surprising that there
might be other oddities to encounter. i'm surprised it's only
become a problem now.
i think you're definitely right. when i change my function
loading program to duplicate all percent signs in all the source
code before passing it to pgdb, they all load successfully and a
subsequent audit of the code inside the database and on disk
still shows that they match, so pgdb must be de-duplicating all
the duplicated percent signs.
thanks so much for spotting this.
cheers,
raf
r protections fail to work or are
bypassed). For a system that's only accessible to internal
staff, it's a great way to defend against their workstations
being infected by malware that goes looking for databases to
attack.
cheers,
raf
Ron wrote:
> On 05/02/2018 04:49 PM, David G. Johnston wrote:
> [snip]
> >
> > - the microsoft patented CSV would be required for implementation. it
> > handles special data with commas and double-quotes in them
> >
> >
> > If true this seems like a show-stopper to anything PostgreSQL w
you just need a function that takes the default
country code or country dialling code and the phone number and
returns what you want to insert and then always use its return
value when assigning a value to the phone_number field. i
suspect that subclassing PhoneNumberField is probably the best
approach.
cheers,
raf
re
from
pg_user u,
pg_proc p
where
u.usename = current_user and
p.proowner = u.usesysid and
p.proname like 'aps%' -- 'aps' is the prefix for our functions
order by
p.proname
Or just: select proname, prosrc from pg_proc where proname like ...
cheers,
raf
when connected to servers
where they mean something? Or am I just misunderstaing
what's happening? I would have thought a new client
would be able to work with an old but supported server.
It's not a big deal. I'll get around to completing the
upgrade and it'll be fine again but I'm curious.
cheers,
raf
or the whole day. And no doubt every country is
different.
All of that is by far the biggest component of such a
calculation. The postgres-specific bit is easy and yes,
what Adrian suggests will be fine if you can use the
dates returned by generate_series to look up the
working conditions of the person involved. I've done it
in plpgsql with a loop over the dates rather using
generate_series in plain sql. Both are fine but plain
sql is probablby faster. Do whatever is most readable.
cheers,
raf
t;t".
Anyone care to explain why the error is what it is?
It's no big deal. I'm just curious.
cheers,
raf
Twitter: http://www.twitter.com/stevelitt
i don't know but voltdb, another in-memory database,
replicates to other instances which can be in different
geographical locations and so not prone to a single
power failure. perhaps all in-memory databases are
aware of the need for this.
cheers,
raf
em apply when the functions are called. This means that
there will never be any successful SQL injection, even if the application
code is buggy, so it's more important for web applications, but I apply
this method to internal systems as well. This approach might help with
scaling because fewer users might be needed but I'm not sure.
cheers,
raf
Rob Sargent wrote:
> On 10/2/19 5:27 PM, raf wrote:
> > >
> > I can't help with questions about scale but I like to give roles/users
> > almost no permissions at all. i.e. They can't select, insert, update
> > or delete anything. All they have permission
27;t be executed at all
and will effectively return null when given a null
argument might be important to know for other functions
as well.
cheers,
raf
Steven Pousty wrote:
> On Sun, Oct 20, 2019 at 4:31 PM raf wrote:
>
> > Steven Pousty wrote:
> >
> > > I would think though that raising an exception is better than a
> > > default behavior which deletes data.
> >
> > I can't help but feel th
Олег Самойлов wrote:
> My vote to change official name form PostgreSQL to PostgresQL. The
> reason is: many young people look at PostgreSQL and think that the
> short name is Postgre. Sounded awfully, especially in Russian, tied to
> fix neighbours. With PostgresQL they will think that the short n
t it
first either in a transaction that will rollback or on
a test server before executing it in production. I
don't think a GUI is suitable for this.
cheers,
raf
It's probably more sensible to grant permissions to roles that
represent groups, and have roles for individual users that
inherit the permissions of the group roles. Then you don't
need to revoke the permissions just because an individiual
has left.
cheers,
raf
AC Gomez wrote:
> T
ecording the timezone is where the
problems come from.
Although having a single timezone for log files is a great idea. They hardly
ever include timezone information so keeping everything in the same timezone
is important. Mind you, they hardly ever even include the year. What's with
that? (yes, I'm looking at you /var/log).
> --
> Tim Cross
cheers,
raf
David G. Johnston wrote:
> On Wed, Apr 15, 2020 at 4:53 PM raf wrote:
>
> > I don't see much difference in storing a timestamptz in UTC or a
> > timestamptz
> > in CET. As long as the intended offset from UTC is recorded (which it is
> > in a timestamptz) it
tters.
The fact that it knows the time zone is what makes everything
work. Timestamp without time zone is best avoided I think.
cheers,
raf
Adrian Klaver wrote:
> On 4/16/20 1:23 AM, raf wrote:
> > Steve Baldwin wrote:
> >
> > > I'm pretty sure you are mistaken. Postgres doesn't store the 'creating'
> > > time zone in a timestamptz column.
> > >
> > > Try do
Marc Munro wrote:
> On Tue, 2020-05-26 at 12:04 -0700, Adrian Klaver wrote:
> > On 5/26/20 12:01 PM, Marc Munro wrote:
> > > I need to be able to cryptographically sign objects in my database
> > > using a public key scheme.
> > > [ . . . ]
> > > Any other options? Am I missing something?
> >
>
re
ports would be needed later. Then the process would
have to be followed twice, once for the first port, and
once again for all the other ports that might become
necessary.
cheers,
raf
t break with such a change.
There might be a reason that the functions needed to be
created as security definers. I'd recommend checking
each function's need to be a security definer before
changing it (or just test it thoroughly somewhere).
cheers,
raf
that it's not useless to make things
harder for a superuser.
You might not stop a legitimate sitewide superuser whose family is being
held hostage, but you can stop, or at least make things much more
difficult, for a superuser process on a single host that is the result
of a software vulnerability that wasn't nobbled by apparmor or selinux
or grsecurity.
cheers,
raf
Laurenz Albe wrote:
> On Mon, 2020-06-22 at 09:44 +1000, raf wrote:
> > A superuser can access files and start programs on the server machine.
> > > A dedicated superuser may for example attach to PostgreSQL with a debugger
> > > and read the value of the variabl
x27;t run out of space,
you might have run out of inodes.
The -i option of df should show you.
100,000 empty files could cause that.
I wouldn't think that renaming would
require a new inode, but I also wouldn't
think that renaming would require any
more space on a file system.
cheers,
raf
FOUTE K. Jaurès wrote:
> Le mer. 1 juil. 2020 à 00:11, raf a écrit :
>
> > FOUTE K. Jaurčs wrote:
> >
> > > Hi everyone,
> > >
> > > I am use a native logical replication in PostgreSQL 12 on Ubuntu 16.04
> > and
> > > all is working fi
e last statement, not the whole transaction. It might
not be exactly what you need.
> As someone working at a large company with an aweful lot of PostgreSQL,
> thinking of the internal production systems I'm aware of, I'd personally
> vote pretty strongly in favor of changing the default.
>
> -Jeremy
>
> Jeremy Schneider
> Database Engineer
> Amazon Web Services
cheers,
raf
ecryptfs works really well with postgres (i.e. no performance penalty that
I could measure) and should probably be usable with docker (but I haven't
done that so I don't know the details).
cheers,
raf
ld make a new
money type interesting. Currencies probably don't
change as often as timezones but there would probably
still be some ongoing need for updates.
cheers,
raf
On Wed, Aug 12, 2020 at 12:44:21PM -0400, Philip Semanchuk
wrote:
> > On Aug 11, 2020, at 8:01 PM, raf wrote:
> >
> > On Tue, Aug 11, 2020 at 06:38:39AM -0700, Miles Elam
> > wrote:
> >
> >> Also of note: PostgreSQL already has a money type (
>
if
db fits entirely in RAM
effective_cache_size = 3GB # Should be 1/2 to 3/4 of RAM
It's the "Should..." comments that matter. And it might be out of date...
Actually, it is out of date. The comment for checkpoint_completion_target
refers to checkpoint_segments which no longer exists (since 9.5) so
disregard that.
cheers,
raf
regards, tom lane
For what it's worth, I have 171305 lines of plpgsql/sql
functions and it hasn't caused any problem on a server
with 4GB RAM. With a small number of long-lived
connections.
cheers,
raf
sed with Postgresql than usual. :-)
cheers,
raf
even if ODBC is really to be considered a second class
> choice. It sounds awkward to me that such a great db has flaws like this (I'm
> NOT stating it *has* flaws)
>
> sandro
Surely, it's a flaw in the C# ecosystem, not a flaw in the database?
(assuming it's true, and assuming there's anything wrong with ODBC). :-)
cheers,
raf
bsite (probably no more than 40
users at a time), and the database became corrupted so
often that I had had to automate rebuilding it from the
latest backup and my own sql logs. I was very silly.
Switching to postgres was the real solution.
cheers,
raf
On Thu, Oct 08, 2020 at 01:14:02AM +0300, Dmitry Igrishin
wrote:
> чт, 8 окт. 2020 г. в 00:14, raf :
> >
> > On Wed, Oct 07, 2020 at 01:53:44PM +0300, Dmitry Igrishin
> > wrote:
> >
> > > In many cases concurrency is not a problem and in fact SQLite may
>
, and then
used the load script to load the unencrypted dump, and
that worked fine.
Does anyone have any idea why this might have happened?
Under what circumstances (immediately after createdb)
might "CREATE SCHEMA public" result in "ERROR: schema
"public" already exists"?
And why might it differ depending on whether the
unencrypted dump was read from stdin or from a file?
cheers,
raf
On Wed, Nov 04, 2020 at 06:29:18PM -0500, Steve Singer
wrote:
>
> It is with much sadness that I am letting the community know that Chris
> Browne passed away recently.
>
> Chris had been a long time community member and was active on various
> Postgresql mailing lists. He was a member of the
an old
database backup (i.e. anything whose timestamp is later
than the timestamp of the backup), which seems like it
might be related to what you are after, but I don't
understand the idea of a "default" date. The "date" for
my schema is always the present so as to match the
corresponding software in its current state.
Perhaps you can explain in more detail what you are
after.
cheers,
raf
y exactly is that a problem in SQL Server?
> And what are the consequences if you do it nevertheless.
According to wikipedia, this problem was discovered on
Halloween day, 1976. I find it hard to believe that any
database would still exhibit that behaviour 44 years
later.
cheers,
raf
cade;
results in:
id | a | b
+---+---
1 | 2 | 2
2 | 3 | 3
3 | 4 | 4
4 | 4 | 5
5 | 5 | 6
6 | 6 | 7
It's the same with or without the index on a(a).
cheers,
raf
If not, it might grant too much access.
You'll also want to make sure that they all have write
access to the same git repository where the views are.
cheers,
raf
> From: Markhof, Ingolf [mailto:ingolf.mark...@de.verizon.com]
> Sent: 07 January 2021 17:19
> To: pgsql-genera
to
store your source code on a file system, so that a
local git repository could see it, and push it to
github. You don't even have to use git if you really
don't want to.
cheers,
raf
from
pg_views v
where
v.viewname like 'myview_%'; -- Your naming convention
Is there a query that can be used to obtain all of the
information needed to reconstruct the create view
statement that corresponds to a view in pg_views?
cheers,
raf
On Sat, Jan 09, 2021 at 05:26:04PM -0700, "David G. Johnston"
wrote:
> On Saturday, January 9, 2021, raf wrote:
>
> > Actually, I just had a look at the pg_views system
> > catalog where the source code for views is stored, and
> > it doesn'
hub.com/lefcha/imapfilter) might be more
appropriate if your email is in an imap account.
cheers,
raf
x27;, Readline will configure the
terminal in a way that will enable it to insert each
paste into the editing buffer as a single string of
characters, instead of treating each character as if
it had been read from the keyboard. This can prevent
pasted characters from being interpreted as editing
commands. The default is `On'.
So try putting this in your ~/.inputrc file:
set enable-bracketed-paste off
cheers,
raf
requirements. However, I still use stored procedures
for everything on websites for security reasons.
Everyone's mileage varies. We're all in different places.
cheers,
raf
at they be fast.
But yes, being able to do complex system testing with transaction
rollback is great.
cheers,
raf
loading program to store its own copy of the source code
in the database somewhere, and just hope that nobody
loads stored procedures using any other tool. Even so,
it will slow down loading old database backups and
bringing their schema and code up to date. But that's
probably OK compared with the time to load the backup
itself.
cheers,
raf
On Fri, Dec 02, 2022 at 03:00:09PM +0200, Pasi Oja-Nisula wrote:
> On Fri, 2 Dec 2022 at 02:24, raf wrote:
> > Same here. Accessing the loaded stored procedure source
> > is how I audit the state of stored procedures in the
> > database against the code in the code reposit
views ... or are your applications completely
> view-free?
In my case, yes, all access is via procedures.
No views or triggers. So it was OK.
cheers,
raf
e adding:
select '';
or
raise notice '';
It won't result in just a blank line, but it will separate things.
cheers,
raf
On Tue, Jan 17, 2023 at 04:10:50PM -0700, "David G. Johnston"
wrote:
> On Tue, Jan 17, 2023 at 4:07 PM raf wrote:
>
> > On Tue, Jan 17, 2023 at 02:22:22PM -0700, "David G. Johnston" <
> > david.g.johns...@gmail.com> wrote:
> >
me, that says that a filename on the command line after the options
will be read as the source of the restore. Only if it is absent would
stdin be used.
Apologies if the usage message for 10.6 doesn't say the same thing.
cheers,
raf
raf wrote:
> Tom Lane wrote:
>
> > David writes:
> > > I have some experience with different versions of Postgres, but I'm just
> > > getting around to using pg_restore, and it's not working for me at all.
> > > ...
> > > But a mat
quot;:
>
> SELECT tellme();
> tellme
> -
> laurenz
> (1 row)
>
> Yours,
> Laurenz Albe
session_user has always worked for me.
cheers,
raf
ematurely.
But the next time I upgrade, I might put the data
directory in a sub-directory of the mountpoint in case
I ever want to start using pg_upgrade. It would also
mean I only need a single ecryptfs mountpoint. At the
moment, I create a new one for each major upgrade which
seems silly.
cheers,
raf
Adrian Klaver wrote:
> On 6/17/19 4:54 PM, Miles Elam wrote:
> > Is there are way to restrict direct access to a table for inserts but
> > allow a trigger on another table to perform an insert for that user?
> >
> > I'm trying to implement an audit table without allowing user tampering
> > with t
Laurenz Albe wrote:
> On Tue, 2019-07-02 at 13:09 +0530, Prakash Ramakrishnan wrote:
> > We are getting below error while during import the csv file please do the
> > needful.
>
> I'd say the needful thing here is for you to read the documentation...
>
> > -bash-4.2$ more ckr_sto.csv
> > 49378
e array elements, assign to it the
record that you want to modify, make the modification
in the single record variable and then assign that
single record variable back into the array element that
it came from.
i.e. something like:
declare
a rectype[];
r rectype;
i integer;
begin
...
r := a[i];
r.field := newvalue;
a[i] := r;
...
I didn't even realise that you could have an array of records.
I've only ever used arrays of scalar values.
cheers,
raf
could be described as a
vendor. By definition, a vendor is a person or
organisation that sells something.
OED: vendor(n): One who disposes of a thing by sale; a seller
cheers,
raf
'-': Broken pipe
gpg: error flushing '[stdout]': Broken pipe
gpg: handle plaintext failed: Broken pipe
And it still doesn't load.
cheers,
raf
On Tue, Sep 03, 2024 at 06:43:22PM -0700, "David G. Johnston"
wrote:
> On Tuesday, September 3, 2024, raf wrote:
>
> > Hi,
> >
> > I need help!
> >
> > I'm upgrading an ancient (but still awesome) postgresql-9.6.24 (via
> > Enterprise
On Tue, Sep 03, 2024 at 10:28:44PM -0400, Tom Lane wrote:
> raf writes:
> > On Tue, Sep 03, 2024 at 06:43:22PM -0700, "David G. Johnston"
> > wrote:
> >> You specified “-1” so I don’t get why you believe pg_restore should be
> >> continuing to execu
there any important different between a stored procedure and
a stored function that returns void? I've been using functions
for everything but calling them procedures in my head (even though
most of them do return a value or result set).
cheers,
raf
On Mon, Mar 08, 2021 at 06:31:56PM -0500, Tom Lane wrote:
> raf writes:
> > Is there any important different between a stored procedure and
> > a stored function that returns void? I've been using functions
> > for everything but calling them procedures in my head (ev
encountered any problems with consistency and
performance issues in doing something like this but I only have
small databases so that doesn't mean there won't be any.
Good luck!
cheers,
raf
74 matches
Mail list logo
