Is such a method of zero-copy texture loading possible/optimal on modern discrete graphics hardware?
You say that as though it were optimal back then. The Intel 740 was not exactly warmly embraced by the market, as noted by the Wikipedia article in question:
The AGP Texture concept soon proved to be a tremendous error in design, because the card had to constantly access the textures over a channel that was upwards of eight times slower than RAM placed on the graphics card itself.
The disparity between available memory bandwidth/performance and available compute resources has only increased in the last 20 years. So, will it be "optimal" to have a discrete GPU read from main memory?
No.
Could it be "possible"? Not in terms of marketability. Even $50 and below discrete GPUs carry their own on-board RAM. Anything much slower/smaller than that isn't worthwhile, because pretty much everyone already has Intel or AMD's on-die GPUs for when performance isn't of importance. If you have a discrete board, it will always be better to put some RAM on the board with its own bus than to have the board try to read most of its data directly from main memory.
Furthermore, could zero-copy texture access functionality be exposed by using mapped buffers in OpenGL?
No. Mapped buffers would be buffers, not textures. They're different kinds of storage objects with (hypothetically) distinct allocations behind them. PBO is a means of asynchronously transferring data from a buffer to a texture. That is a transfer of data, not an adoption of a memory allocation.
Intel has an extension for mapping a texture's storage, but that requires that the texture be in a linear format. It's not widely supported, and likely never will be.
Vulkan provides for the possibility of something similar, but using linear texture as sources in sampler read operations from a shader is not required to be supported. You'd have to ask each implementation whether it allows it and in which formats, and for which usages are allowed.