On Thu, Jul 18, 2024 at 4:38 AM sivapostg...@yahoo.com
wrote:
>
> Hello,
> PG V11
>
> Select count(*) from table1
> Returns 10456432
>
> Select field1, field2 from table1 where field3> '2024-07-18 12:00:00'
> Times out
>
> The above query was working fine for the past 2 years.
>
> Backup was taken
On Sun, Jul 7, 2024 at 4:13 AM Pavel Stehule wrote:
>
> but looks so there are false alarms related to using an alias. It is
> interesting so I have not any report about this issue, so probably using
> aliases is not too common today.
I'm not sure why there's a warning about using an alias. 43.
In the following short code, the out parameter d3 is also declared, so
its value will be null in the output.
create or replace function public.test_function(in varchar, in char, in date, ou
t d1 text, out d2 integer, out d3 char, out d4 date )
stable as $$
DECLARE
wk_intxt alias for $1;
wk
I don't have any direct experience with Yugabyte (the databases I work
with are way too small to be on Yugabyte) but my older son does work
for them as an SRE, sometimes remotely when he's visiting us, so we've
talked about it a bit. (It's actually the first time in 20 years I've
had much of a clu
My production server has a materialized view that is refreshed from a
mysql_fdw several times a day.
What I'd like to be able to do is refresh the materialized view on the
testbed server from the one on the production server so that they are
the same. (Refreshing it from the MySQL server will res
Followup: The problem turned out to be a field in the mysql server
that was not size-limited to 255 characters and had several rows with
as many as 299 characters in them. Apparently when using an FDW and
materialized view in postgresql 10.4, field size limits aren't
checked, because a query on t
On Thu, Jan 25, 2024 at 3:38 PM Tom Lane wrote:
>
> Michael Nolan writes:
> > On the 10.4 server this materialized view works, but on the 16.1
> > server it fails:
>
> > uscf=# refresh materialized view uscf_vip;
> > refresh materialized view uscf_vip;
&
I'm in the process of updating a server from postgresql 10.4 on Centos
7 to 16.1 on AlmaLInux 9, and am reminded of the old line about when
you're up to your necks in alligators, it is difficult to remember
that your goal was to drain the swamp.
We have several materialized views that are refreshe
No, it wasn't there, because I hadn't included --with-openssl in the
configure. Looking at my history, I had done that once earlier but
dropped it for the reason noted below.
Including --with-openssl does include the crypto library, but if I
don't do a 'make clean' before doing a make, I get erro
Sorry, I meant 'make check'. :sigh:
Writing or debugging makefiles is something I haven't done much of,
but as best I can figure out the problem is that the libcrypto.so file
isn't being linked in, though this line in the Makefile in pgcrypto
seems to say should be:
SHLIB_LINK += $(filter -lcrypto -lz, $(LIBS))
I'm guessing it is s
On Thu, Jan 18, 2024 at 9:51 AM Tom Lane wrote:
>
> Looks like we have aarch64 and ppc64 machines running Alma 8 and 9.
> No x86 though, which might matter for such a low-level failure
> as this.
So I guess that'll be on the list to add to the build farm at some
point? (My Xanthian 'talent' of f
On Thu, Jan 18, 2024 at 5:03 AM Daniel Gustafsson wrote:
>
> > On 18 Jan 2024, at 00:59, Michael Nolan wrote:
> > On Wed, Jan 17, 2024 at 5:32 PM Daniel Gustafsson wrote:
> >>
>
> That's surprising, I expected that it would require the legacy provider be
On Wed, Jan 17, 2024 at 5:32 PM Daniel Gustafsson wrote:
>
> > On 18 Jan 2024, at 00:24, Michael Nolan wrote:
> >
> > Sorry for the delay in responding, network issues kept me offline for
> > several days.
> >
> > These are the openssl packages inst
Sorry for the delay in responding, network issues kept me offline for
several days.
These are the openssl packages installed from the Almalinux 9 repositories:
apr-util-openssl.x86_64 1.6.1-23.el9 @appstream
openssl.x86_641:3.0.7-24.el9
This is on AlmaLinux 9.3, installing postgresql from source code.
In PG 16.1 when I try to install pgcrypto, the modules compile but I
get this error when running checks:
CREATE EXTENSION pgcrypto;
+ERROR: could not load library
"/home/postgres/src/postgresql-16.1/tmp_install/usr/local/pgsql/li
I'm trying to install mysql_fdw_16 and it requires the mariadb-devel module.
There is no mariadb-devel module for Almalinux 9. There is a
MariaDB-devel module, but when I install it, the install for
mysql_fdw_16 still tells me that there is no mariadb-devel module
available.
How do I work around
Sorry for the top posting, I forget that Gmail does that by default.
Mike Nolan
To add to what Steve has said, see if you can find a room with
equipment similar to what you'll be using for the actual presentation
to practice in. Put up your most complex slide, then take a seat in
the back of the room and imagine what it'll look like with a room full
of people in front of you.
I'm not clear which rows are duplicates.
It looks like limb_id, branch_id and twig_id make up a unique key.
If there was a row (2, 2, 7,103.7) would it be a duplicate of rows
(1,1,2,103.7) and (1,1,3,103.7)?
--
MIke Nolan
It's not just Ruby, dumb databases are preferred in projects like
WordPress, Drupal and Joomla, too.
Now, if it's because they're used to using MySQL, well maybe that's
not so hard to understand. :-)
On Mon, Jun 26, 2023 at 8:05 PM Guyren Howe wrote:
>
> This is a reasonable answer, but I want
Can you use a CASE statement? The real issue with date conversion is
not knowing if a value of 02-03-2023 is mm-dd- or dd-mm-.
On Wed, Jun 14, 2023 at 11:42 AM Marc Millas wrote:
>
> Hi,
>
> I would like to load data from a file via file_fdw or COPY.. its a postgres
> 14 cluster
>
> but
You're gonna lock yourself into SOMETHING, that's why there are still
thousands of COBOL programs still being maintained.
Mike Nolan
On Fri, Jun 9, 2023 at 3:39 PM Ron wrote:
>
> You can be sure that banks and academic research projects have different
> needs. Heck, your University's class sch
Clearly I'm a 73 year old dinosaur, because I believe in having the
business logic in the database wherever possible. But the development
projects I've been around lately aren't using triggers at all. (And
it should not surprise anyone, certainly not me, that consistency of
data enforcement is an
On Fri, Oct 21, 2022 at 1:52 PM Julie Nishimura
wrote:
> Hello,
> A table is constantly updated by inserting new rows. Will it affect select
> if where clause is based on > date?
> Does "where clause" to specify > date and < now?
>
You haven't described the table. Is there some kind of a date o
Having RPMs available for an RC version seems premature to me. This is the
first site I've run where I've installed postgresql via dnf, will it run
the upgrade script when it installs a new release?
--
Mike Nolan
Then it looks like the postgresql home page is out of date, it still says
RC1 is official as of Sep 29th with a planned release date of October 13th.
--
Mike Nolan
I was doing a check on updates available on my Centos 8 server and dnf is
telling me that Postgresql 15 is available.
I thought it was only at the RC1 state, but here's what I get:
PostgreSQL 15 for RHEL / Rocky 8 - x86_6417 B/s | 195 B 00:11
PostgreSQL 15 for RHEL / Rocky 8 - x86_
On Wed, Jul 27, 2022 at 7:50 AM Wiwwo Staff wrote:
> Since changing ph_hda.conf file to give users access involves the restart
> of server, many companies I work(ed) use a bastion host, where users ssh
> to, and are allowed "somehow" use postgresql.
>
> Still, those users need some login shell.
>
I do not know those other tools, but there should be documentation for
them, as there is in the man page for how to process just one table using
pg_dump. You can pipe the output of pg_dump directly to psql, but I find
that's seldom useful.
--
Mike Nolan
On Sun, Jul 10, 2022 at 10:28 AM Igor Korot wrote:
> Hi,
> Is there a query I can execute that will give me CREATE TABLE() command
> used to create a table?
>
> Thank you.
>
Use pg_dump --schema-only
--
Mike Nolan
I had a client that was stuck on 8.2 for a long time, when they finally
upgraded to 10, it took several weeks of testing to find things that needed
to be changed in scripts, functions and PHP programs. And even then we
were still finding type cast issues for another year in seldom-used SQL
code.
Thanks for confirming my suspicions, I'm working on a plan B to deal with
this as best I can.
--
Mike Nolan
On Mon, Apr 4, 2022 at 9:23 AM Laurenz Albe
wrote:
> On Mon, 2022-04-04 at 09:18 -0500, Michael Nolan wrote:
> > Is it not recommended to use a FDW table in a trigger functi
Background: About two years ago the membership system I developed for a
client was moved from our in-house postgresql app to civi-CRM, which uses
MySQL. (I'm semi-retired, the move to civi-CRM is part of a long term
technology change.)
We have a FDW that simulates the old membership table that c
One of the grad students in the computer center had a sign on his wall:
God is real, but Man is only an integer.
--
Mike Nolan
In the same 1971 seminar where we studied Algol-68, we had to read and
write a short paper on the 1970 Codd paper on relational theory, which had
only been out for about a year. The professor running the seminar noted
that Codd proved that the relational model worked, but didn't guarantee
that it
On Wed, Sep 15, 2021 at 7:31 PM FWS Neil wrote:
>
> Programmers create a dozens of new languages every 10 years or so. Only a
> few have stood the test of time. SQL is one of those. For all its faults,
> it still is amazingly powerful.
>
> Neil
> www.fairwindsoft.com
>
>
Dennis Ritchie was giv
When I was working at the help desk at the computer center as an undergrad,
the professor in charge of that group used to give us interesting little
language tests for things we needed to watch out for, especially with
beginning programmers.
One of his favorite ploys was to use the EQUIVALENCE fun
Of all the languages I wrote in, I think SNOBOL was the most fun to write
in, and LISP the least fun. Control Data assembler language
programming was probably the most precise, because you could crash the OS
with a single mis-placed character, something I did more than once.
In a graduate-level c
I started programming in 1967, and over the last 50+ years I've programmed
in more languages than I would want to list. I spent a decade writing in
FORTRAN on a GA 18/30 (essentially a clone of the IBM 1130) with limited
memory space, so you had to write EFFICIENT code, something that is a bit
of
On Sat, May 29, 2021 at 9:15 AM Laura Smith <
n5d9xq3ti233xiyif...@protonmail.ch> wrote:
> Hi
>
> I've got a bit of a puzzle that I'm not quite sure how to approach.
>
> Let's say I've got a table of bios, so :
>
> create table bios (
> first_name text not null,
> last_name text not null,
> person
> What can and does happen is that a new version is released under a
different license while the old version is made obsolete.
It is often more than just 'made obsolete', updates to other parts of the
OS (that are almost impossible to avoid installing) can make it so those
older products either d
There's so much garbage in a Google search any more that they're becoming
nearly useless. Between 'sponsored' hits and ones that have little or no
relevance but throw in words to get included, I find as often as not that
IF Google finds what I'm looking for, it'll be several pages in.
At some poi
>
> Sorry about the top-posting, Firefox and I disagreed about whether I was
> done editing the previous message.
>
--
Mike Nolan
I usually use something like trim(field) like 'pattern'. Eliminates the
need for the wildcard at the end. I find I use the ~ operator more than
'like' though.
--
Mike Nolan
On Mon, Aug 10, 2020 at 12:24 PM Adrian Klaver
wrote:
> On 8/10/20 10:01 AM, Michael Nolan wr
The problem is your field is fixed length text, change it to varchar(100)
or text and it works without the wildcard at the end.
--
Mike Nolan
Thanks, for the time being we're looking at using something other than the
FDW for this task.
--
Mike Nolan
An application I wrote is being modified by our development team to use a
FDW to a remote MySQL table instead of the postgresql table for a query.
We're getting this error in the logs periodically. Maybe about once every
50,000 queries:
2020-07-14 11:35:22.799 CDT uscf ::1 ERROR: failed to exec
On Wed, Jun 3, 2020 at 5:21 PM Martin Mueller <
martinmuel...@northwestern.edu> wrote:
> On the topic of what other databases do better: I much prefer Postgres to
> Mysql because it has better string functions and better as well as very
> courteous error messages.
>
Martin, I definitely sympathiz
Recently I was typing in a query in PG 10.4.
What I MEANT to type was: Where xyz >= 2400
What I actually typed was: Where xyz >- 2400
The latter was interpreted as 'where xyz > -2400', but I'm wondering if it
shouldn't have thrown an error on an unrecognized operator '>-'
Thoughts?
--
Mike N
I spent about 10 years as an Oracle DBA (back around Oracle 7 and 8) and
the last 20 or so years doing PostgreSQL.
My initial impressions were that Oracle did a better job providing tools
and options that users and DBAs need and PostgreSQL was pretty much
roll-your-own.
Things like being able to
On Sat, May 16, 2020 at 10:19 AM Alex Magnum wrote:
> Hi,
>
> I have a string that I want to cut to 60 char and then remove the last
> field and comma.
>
> substring('Class V,Class VI,Class VII,Competitive Exam,Class VIII,Class
> X,Class XI,Class IX,Class XII',1,60);
>
> substring | Class V,Class
On Fri, May 15, 2020 at 12:51 PM Ravi Krishna
wrote:
>
> Why should the backup land in S3, and not local somewhere?
> Any good reason why one should pay for the additional storage and transfer
> costs?
>
> Good question. The key point in my statement was "db of this size".
>
> The problem with lo
On Thu, Jan 16, 2020 at 6:28 AM stan wrote:
> I am trying to create a function to automatically create a reference value
> when a record is inserted into a table. I want the reference value to
> consist of the user that is doing the insert, plus a couple of dates, plus
> a sequence number, where
On Sun, Dec 1, 2019 at 8:09 AM Martin Gainty wrote:
>
> that said I think MS missed the boat on 2-phase-commits
>
Microsoft has never really embraced the concept of a multi-user database
environment. (It doesn't really understand the concept of a multi-user
operating system, either.)
--
Mike N
> Considering that the auditing needs to be the last, how can I be sure it´ll
> ran lastly ?
>
IMHO, auditing should be done in after- triggers, when that the data in the
records being inserted, updated or deleted can't be changed but you can
still write to the separate auditing tables.
--
Mike No
A few years ago I tried to take an app someone had written for us in
Access years ago and convert it to Postgres.
It seemed like for every rule I tried there were a handful of exceptions.
We wound up just rewriting the app and not trying to export the data from
the previous one.
I hope your pro
A cron job will only run once a minute, not wake up every second. But you
could write a PHP program that does a one-second sleep before checking if
there's something to do, and a batch job that runs periodically to see if
the PHP program is running, and if not, launch it.
That's how I handle a jo
On Mon, May 6, 2019 at 6:05 AM Arup Rakshit wrote:
SELECT MAX(id) FROM chinese_price_infos; max 128520(1 row)
SELECT nextval('chinese_price_infos_id_seq'); nextval - 71164(1
row)
Not sure how it is out of sync. How can I fix this permanently. I ran
vacuum analyze verbose;
I'm still not clear what the backslash is for, it is ONLY to separate first
and last name? Can you change it to some other character?
Others have suggested you're in a Windows environment, that might limit
your options. How big is the file, is it possible to copy it to another
server to manipul
On Fri, May 3, 2019 at 9:35 AM Ravi Krishna wrote:
> >
> > In what format are you dumping the DB2 data and with what specifications
> e.g. quoting?
> >
>
> DB2's export command quotes the data with "". So while loading, shouldn't
> that take care of delimiter-in-the-data issue ?
>
I don't think
Assuming you get the database back online, I would suggest you put a
procedure in place to monitor disk space and alert you when it starts to
get low.
--
Mike Nolan
On Sun, Apr 14, 2019 at 4:06 AM Peter J. Holzer wrote:
>
> If you want to prevent a user from logging in (which is functionally
> equivalent but a bit stronger than "instantly kick off"), then this is
> definitely something that could and should be implemented via PAM (I'm
> not sure what informa
The MySQL manual says that INNODB 'adheres closely' to the ACID model,
though there are settings where you can trade some ACID compliance for
performance.
See https://dev.mysql.com/doc/refman/5.6/en/mysql-acid.html
I've been running PostgreSQL for a client since 2005, we're on our 5th
hardware pl
On Mon, Mar 11, 2019 at 2:20 PM Gavin Flower
wrote:
> On 12/03/2019 05:35, Michael Nolan wrote:
> [...]
> > MySQL is better at isolating users from each other and requires less
> > expertise to administer.
>
> [...]
>
> I keep reading that MySQL is easier to
On Mon, Mar 11, 2019 at 6:32 AM Sonam Sharma wrote:
> Hi All,
>
> We are planning to migrate our database into any open source DB.
> Can someone please help me in knowing which one will be better among
> POSTGRESQL and MYSQL.
>
> In what terms postgres is better than MYSQL.
>
> Regards,
> Sonam
>
On Tue, Nov 6, 2018 at 11:40 AM Tom Lane wrote:
>
> That represents setting the yes-this-row-is-committed hint bits on the
> newly loaded rows. The first access to any such row will set that bit,
> whether it's a select or a VACUUM or whatever.
>
> Tom, does that include ANALYZE?
--
Mike Nolan
On Tue, Nov 6, 2018 at 11:08 AM Ravi Krishna wrote:
> PG 10.5
>
> I loaded 133 million rows to a wide table (more than 100 cols) via COPY.
>
It's always a good idea after doing a large scale data load to do a vacuum
analyze on the table (or the entire database.)
--
Mike Nolan
On Tue, Oct 23, 2018 at 6:36 PM Ravi Krishna wrote:
>
> I have hardly used savepoints in any application, but if I understand it
> correctly, isn't it something which is typically used
> in a persistent connection. I wonder how it is applicable in a web based
> stateless application like Amazon.
On Mon, Jun 4, 2018 at 12:15 PM, Tom Lane wrote:
> Michael Nolan writes:
> > Microsoft has bought GitHub for $7.5 billion, is this a threat to the
> open
> > source community?
>
> A fair question, but one that seems entirely off-topic for the Postgres
> lists, sinc
Microsoft has bought GitHub for $7.5 billion, is this a threat to the open
source community?
--
Mike Nolan
Just call me blind!
--
Mike Nolan
On Sat, May 19, 2018 at 5:23 PM, Adrian Klaver
wrote:
> On 05/19/2018 03:16 PM, Michael Nolan wrote:
>
>>
>>
>> On Sat, May 19, 2018 at 2:16 PM, Don Seiler > d...@seiler.us>> wrote:
>>
>> On Sat, May 19, 2018 at 2:10 PM, Michael
On Sat, May 19, 2018 at 2:16 PM, Don Seiler wrote:
> On Sat, May 19, 2018 at 2:10 PM, Michael Nolan wrote:
>
>> initdb is failing on Centos 7 with 10.4 because the install procedure
>> does not change the ownership of the /usr/local/pgsql directory to
>> postgres.
>
initdb is failing on Centos 7 with 10.4 because the install procedure does
not change the ownership of the /usr/local/pgsql directory to postgres.
Changing the ownership fixes the problem, but the install procedure should
do this.
--
Mike Nolan
On Mon, Apr 16, 2018 at 6:58 PM, Ron wrote:
> We're upgrading from v8.4 to 9.6 on a new VM in a different DC. The dump
> file will be more than 1TB, and there's not enough disk space on the
> current system for the dump file.
>
> Thus, how can I send the pg_dump file directly to the new server w
On Tue, Dec 19, 2017 at 1:24 PM, Kevin Burke wrote:
> I'm writing a function that looks a little like this:
>
>
> This seems pretty cumbersome. Is there an easier way I am missing?
> Specifically it would be neat if it was easier to visualize the
> intermediate steps in the query production. If t
On Mon, Dec 18, 2017 at 6:23 AM, Nick Dro wrote:
> Hi,
> Why PostgreSQL doesn't have build-in function to calculate percentage?
> somthing like percent(number,%
> for example:
> select percent(100,1) will calculate 1% of 100 = 1
> select percent(25,20) will calculate 20% of 25 = 5
>
> Seems like
On Fri, Nov 24, 2017 at 8:54 AM, hmidi slim wrote:
> I'm trying to analyze some queries using the explain instructions and the
> option analyze and buffers. I realized that the query refers to the cache
> memory to return the results. Is there any solution to clear the cache in
> postgresql inord
On Mon, Nov 20, 2017 at 9:45 AM, Stephen Frost wrote:
>
> This list has now been migrated to new mailing list software known as
> 'PGLister'. This migration will impact all users of this mailing list
> in one way or another.
>
Is there more information available about PGLister somewhere, ie, is
80 matches
Mail list logo
