Can you refresh a materialized view from a materialized view on another server?

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

Re: field error on refreshed materialized view

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

Re: field error on refreshed materialized view

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; &

field error on refreshed materialized view

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

Re: undefined symbol when installing pgcrypto on 16.1

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

Re: undefined symbol when installing pgcrypto on 16.1

Sorry, I meant 'make check'. :sigh:

Re: undefined symbol when installing pgcrypto on 16.1

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

Re: undefined symbol when installing pgcrypto on 16.1

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

Re: undefined symbol when installing pgcrypto on 16.1

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 >

Re: undefined symbol when installing pgcrypto on 16.1

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

Re: undefined symbol when installing pgcrypto on 16.1

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

undefined symbol when installing pgcrypto on 16.1

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

looking for mariadb-devel to install mysql_fdw_16 in AlmaLinux 9

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

Re: Presentation tools used ?

Sorry for the top posting, I forget that Gmail does that by default. Mike Nolan

Re: Presentation tools used ?

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

Fwd: Need efficient way to remove (almost) duplicate rows from a table

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

Re: Large scale reliable software system

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

Re: date format

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 > >

Re: Question about where to deploy the business logics for data processing

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

Re: Question about where to deploy the business logics for data processing

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

Re: constantly updated table in Amazon RDS

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

Re: postgresql 15 for RHEL RPMs available?

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

Re: postgresql 15 for RHEL RPMs available?

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

postgresql 15 for RHEL RPMs available?

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 -

Re: Feature request: psql --idle

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.

Re: Get the table creation DDL

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

Re: Get the table creation DDL

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

Re: How to upgrade postgres version 8 to 13

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.

Re: Trigger functions and FDW tables

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 function? &

Trigger functions and FDW tables

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

Re: Alter and move corresponding: was The tragedy of SQL

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

Re: The tragedy of SQL

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

Re: The tragedy of SQL

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

Re: Alter and move corresponding: was The tragedy of SQL

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

Re: The tragedy of SQL

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

Re: The tragedy of SQL

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

Re: Overriding natural order of query results for a subset

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, >

Re: Copyright vs Licence

> 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

Re: Do we need a way to moderate mailing lists?

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

Re: SQL Question about like

> > Sorry about the top-posting, Firefox and I disagreed about whether I was > done editing the previous message. > -- Mike Nolan

Re: SQL Question about like

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 wrote: > > Th

Re: SQL Question about like

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

Re: Problem with FDW wrapper errors

Thanks, for the time being we're looking at using something other than the FDW for this task. -- Mike Nolan

Problem with FDW wrapper errors

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

Re: Oracle vs. PostgreSQL - a comment on Mysql

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

A parsing question

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

Re: Oracle vs. PostgreSQL - a comment

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

Re: Removing Last field from CSV string

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

Re: Inherited an 18TB DB & need to backup

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

Re: Can I do this?

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

Re: MS Access Frontend

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

Re: naming triggers for execution

> 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

Re: Converting Access .mdb to postgres

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

Re: How to run a task continuously in the background

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

Re: duplicate key value violates unique constraint "chinese_price_infos_pkey"

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;

Re: Back Slash \ issue

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

Re: Back Slash \ issue

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

Re: Starting Postgres when there is no disk space

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

Re: Trigger when user logs in

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

Re: POSTGRES/MYSQL

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

Re: POSTGRES/MYSQL

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

Re: POSTGRES/MYSQL

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

Re: why select count(*) consumes wal logs

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

Re: why select count(*) consumes wal logs

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

Re: Oracle vs PG

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

Microsoft buys GitHub, is this a threat to open-source

Microsoft has bought GitHub for $7.5 billion, is this a threat to the open source community? -- Mike Nolan

Re: initdb failing (10.4 centos7)

Just call me blind! -- Mike Nolan

Re: initdb failing (10.4 centos7)

On Sat, May 19, 2018 at 5:23 PM, Adrian Klaver <adrian.kla...@aklaver.com> 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 > d...@seiler.us>> wrote: >> >>

Re: initdb failing (10.4 centos7)

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 Nolan <htf...@gmail.com> wrote: > >> initdb is failing on Centos 7 with 10.4 because the install procedure >> does not change the ownership of

Re: pg_dump to a remote server

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

Re: How clear the cache on postgresql?

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